March 21, 2014 at 6:45 pm
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
for system in ([system 1], [system 2], [system 3])
)p
union all
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 2'
) x
pivot
(
max(item)
for system in ([system 1], [system 2], [system 3])
)p
union all
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 3'
) x
pivot
(
max(item)
for system in ([system 1], [system 2], [system 3])
)p
Which produces this result:
SYSTEM 1SYSTEM 2SYSTEM 3
item 1item 1item 1
(null)item 2item 2
item 3item 3(null)
March 21, 2014 at 7:01 pm
Using CROSS TABS you could achieve this easily without UNION ALL.
SELECT MAX( CASE WHEN system = 'system 1' THEN item END) system1
,MAX( CASE WHEN system = 'system 2' THEN item END) system2
,MAX( CASE WHEN system = 'system 3' THEN item END) system3
FROM test
GROUP BY item
That's one more reason of why I prefer cross tabs over pivot.
If you have any questions, feel free to ask.
March 21, 2014 at 7:05 pm
You know, I've always known of CASE, but I've never found a way to implement it.
This works perfectly! Thank you Luis Cazares!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply