CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
user2731291
user2731291

Reputation: 11

T-SQL Displaying data in one column on the condition/data of another column

Displaying Data in one column on the condition/data of another column

I am looking to select data from rows and display them in a column that depends on data in an adjacent column.

Example :

Here is my data :

ID            STATUS      VALUE
XYZ            1          7.50
XYZ            2          65.00
XYZ            3          795.00
ABC            1          15.60
ABC            3          563.50
ABC            2          79.00
DEF            3          597.00
DEF            1          23.00
DEF            2          89.60

The result I am looking for is:

(1=ValueA, 2=ValueB, 3=ValueC)

ID            VALUEA      VALUEB      VALUEC
XYZ            7.50        65.00      795.00
ABC            15.60       79.00      563.50
DEF            23.00       89.60      597.00

I have found entries on this site that deal with similar questions, however don't quite fit for this scenario. Is there a simple query that will produce the desired result?

Upvotes: 1

Views: 750

Answers (3)

Taryn
Taryn

Reputation: 247720

You should be able to use an aggregate function with a CASE expression to get the result:

select id,
  max(case when status = 1 then value end) ValueA,
  max(case when status = 2 then value end) ValueB,
  max(case when status = 3 then value end) ValueC
from yourtable
group by id;

See SQL Fiddle with Demo. The CASE expression will check for each status and then select the max value associated. The transformation of data from rows into columns is called a PIVOT.

You could also use the PIVOT function to get the result:

select id,
  [1] ValueA,
  [2] ValueB,
  [3] ValueC
from yourtable
pivot
(
  max(value)
  for status in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo.

Finally, you could also JOIN on your table multiple times to return the result:

select a.id,
  a.value ValueA,
  b.value ValueB,
  c.value ValueC
from yourtable a
left join yourtable b
  on a.id = b.id
  and b.status = 2
left join yourtable c
  on a.id = c.id
  and c.status = 3
where a.status = 1;

See SQL Fiddle with Demo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You are looking to pivot the data. One way to do this is by use aggregation with conditional max():

SELECT  id ,
    MAX(CASE WHEN status = 1 THEN Value
        END) AS ValueA ,
    MAX(CASE WHEN status = 2 THEN Value
        END) AS ValueB ,
    MAX(CASE WHEN status = 3 THEN Value
        END) AS ValueC
FROM    t
GROUP BY id;

Upvotes: 1

Monty Wild
Monty Wild

Reputation: 4001

You appear to be looking for the PIVOT statement.

Upvotes: 0

Related Questions