CODEWITHSUNDEEP
sqlsql-server
ShB
ShB

Reputation: 319

SELECT multivalues from same table in one row

I have the below table. as a reference table for values in the system.

Item    Item_code
Turkey  c01
KSA     c02
USA     c03
NY      s01
JED     s03
Dubai   j01
London  j02
.       .
.       .
.       .

In the display page for users I want to show readable data " KSA, NY ..." not the codes. having the results in one row would save a lot code line.I don't want to do 5 select statements to get each value separately

example: I want to display the Item column of c02, s01 and s03 in one row.

desired result :

first Item     second Item    third Item ...
KSA             JED           NY

this goes for 5 columns.

Upvotes: 0

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One method uses conditional aggregation with the ANSI-standard row_number() function:

select max(case when seqnum = 1 then item end) as item_1,
       max(case when seqnum = 2 then item end) as item_2,
       max(case when seqnum = 3 then item end) as item_3,
       max(case when seqnum = 4 then item end) as item_4,
       max(case when seqnum = 5 then item end) as item_5
from (select t.*, row_number() over (order by item_code) as seqnum
      from t
       where item_code in ('c02', 's01', 's03')
     ) t;

Upvotes: 4

Tree Frog
Tree Frog

Reputation: 666

You can use a pivot table like so

select * from (
    select item, code from @table
) as x
pivot
(
    max(item)
    for code in (c02, s03, s01)
) as pvt

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

What about conditional MAX()

 SELECT MAX( CASE WHEN Item_code = 'c02' THEN Item END ) as first_item,
        MAX( CASE WHEN Item_code = 's01' THEN Item END ) as second_item,
        MAX( CASE WHEN Item_code = 's03' THEN Item END ) as third_item
 FROM Items

Upvotes: 2

Related Questions