Home Creating a pivot based on rules
Reply: 3

Creating a pivot based on rules

l--''''''---------''''''''''''
1#
l--''''''---------'''''''''''' Published in 2017-12-06 19:37:44Z

I have a dataset like:

+---------------+----+
| Grade         | ID |
+---------------+----+
| Copper        | 1  |
+---------------+----+
| Copper        | 2  |
+---------------+----+
| Copper Bonded | 2  |
+---------------+----+
| Fiber         | 3  |
+---------------+----+
| Fiber         | 4  |
+---------------+----+

I would like a result like this:

+----+--------+
| ID | Copper |
+----+--------+
| 1  | A      |
+----+--------+
| 2  | B      |
+----+--------+
| 3  | C      |
+----+--------+
| 4  | C      |
+----+--------+

The ruleset is something like this:

A :  Copper only
B :  Copper and Copper Bonded
C :  Fiber only

Is this a candidate for a pivot, how do I express this as a sql query?

KeithL
2#
KeithL Reply to 2017-12-06 20:28:35Z

I used a delimited trick and evaluated that:

create table #temp  (grade varchar(100),id int)
insert into #temp
values
('Copper',1)
,('Copper',2)
,('Copper bonded',2)
,('Fiber',3)
,('Fiber',4)

;with cte as
(
select distinct id,stuff((select '|' + grade
                from #temp
                where id = t1.id
                group by grade
                order by grade
                for xml path('')
                ),1,1,'') as Combined
from #temp t1
)

select id,
    case combined
        when 'Copper' then 'A'
        when 'Copper|Copper Bonded' then 'B'
        when 'Fiber' then 'C'
        else 'Unknown'
    end as Copper 
from cte



drop table #temp

Results:

id  Copper
1   A
2   B
3   C
4   C
Tab Alleman
3#
Tab Alleman Reply to 2017-12-06 20:04:42Z

Ok you definitely need to use CASE, and it is complex, and you have only given us a "something like this" ruleset, so I will give you some psuedocode to point you in the direction of a solution, and your final code will be "something like this" depending on your actual rules:

SELECT DISTINCT ID, 
  CASE 
    WHEN EXISTS(SELECT a row with this ID WHERE Grade='Copper')
      AND EXISTS(SELECT a row with this ID WHERE Grade='Copper Bonded')
      AND NOT EXISTS(SELECT a row with this ID WHERE Grade is something other than 'Copper' or 'Copper Bonded')
        THEN 'B'
    WHEN EXISTS(SELECT a row with this ID WHERE Grade='Copper')
      AND NOT EXISTS(SELECT a row with this ID WHERE Grade is something other than 'Copper')
        THEN 'A'
    WHEN ... {by now you should be starting to get the idea}
  END AS Copper
FROM MyTable t1  --be sure to alias your table so you can correlate your EXISTS subqueries
Sami
4#
Sami Reply to 2017-12-06 20:21:42Z

Its really weird case and conditions but here is an idea:

CREATE TABLE T (
    ID INT IDENTITY (1,1),
    GRADE VARCHAR(25)
    );
INSERT INTO T VALUES
('Copper'),
('Copper'),
('Copper Bonded'),
('Fiber'),
('Fiber');
with cte as (
SELECT ID, GRADE, row_number () over (partition by grade order by id) rn
FROM T
),
Final as (
select id, case when grade = 'Copper'and rn = 1 then 'A' 
                when grade= 'Copper Bonded' then 'B'
                when grade = 'Fiber' then 'C' end k
from cte)

select  row_number () over (order by k) id,k as Copper 
from final
where k is not null

Results:

+----+--------+
| id | Copper |
+----+--------+
|  1 | A      |
|  2 | B      |
|  3 | C      |
|  4 | C      |
+----+--------+
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30831 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO