How to get the selected data into a single row using SQL

Question

I am trying to import some data to Excel from a database using SQL.

I am getting the data in the following columns:

Item# Location Qty

but I would like to import it in the following format:

Item# Qty@Location1 Qty@Location2 Qty@Location3

Select Distinct item#,
CASE WHEN location="location1" THEN qty END AS QTY@Location1,
CASE WHEN location="location2" THEN qty END AS QTY@Location2,
CASE WHEN location="location3" THEN qty END AS QTY@Location3
From table1

This, however returns 3 records.Is there any way to merge these to a single row?

possible duplicate of [How to pivot table with T-SQL?](http://stackoverflow.com/questions/9830960/how-to-pivot-table-with-t-sql) — Michael Green, May 01 '14 at 14:42

score 0 · Answer 1 · answered May 01 '14 at 12:10

You want to do an aggregation:

Select item#,
       MAX(CASE WHEN location="location1" THEN qty END) AS QTY@Location1,
       MAX(CASE WHEN location="location1" THEN qty END) AS QTY@Location1,
       MAX(CASE WHEN location="location1" THEN qty END) AS QTY@Location1
From table1
Group By item#;

score 0 · Answer 2 · answered May 01 '14 at 12:39

Yes, I think agregating the rows will work

Select item#,
Sum(CASE WHEN location="location1" THEN qty else 0 END) AS QTY@Location1,
Sum(CASE WHEN location="location1" THEN qty else 0 END) AS QTY@Location1,
Sum(CASE WHEN location="location1" THEN qty else 0 END) AS QTY@Location1
From table1
group by
item#

score 0 · Answer 3 · answered May 01 '14 at 12:59

assuming that ´location1´ ´location2´ and ´location3´ are constants you can do:

SELECT isnull(isnull(loc1.Item, loc2.Item), loc3.Item)  as Item,
       loc1.Qty as QTY@Location1, loc2.Qty as QTY@Location2, loc3.Qty as QTY@Location3
FROM 
  (select Item, Qty from table1 where Location = 'location1') as loc1
FULL JOIN
  (select Item, Qty from table1 where Location = 'location2') as loc2 ON loc1.Item = loc2.Item
FULL JOIN
  (select Item, Qty from table1 where Location = 'location3') as loc3 ON isnull(loc1.Item, loc2.Item) = loc3.Item

score 0 · Answer 4 · answered May 01 '14 at 14:41

0

A PIVOT will do this for you quite neatly, if your DBMS supports it. [Here's] an SO question on the subject.

answered May 01 '14 at 14:41

Michael Green

1,397
1
17
25

How to get the selected data into a single row using SQL

4 Answers4