Display table rows as columns based on some id
I have a table like
ID option
1 optionA
1 optionB
1 optionC
1 optionD
And I want to get the result:
ID A B C D
1 optionA optionB optionC optionD
What's the best way to do this?
The request I tried is
select * from TableName PIVOT (option for ID = 2674 )) as abc
it won't work as PIVOT expects an aggregated function.
I also tried COALESCE like this one
declare @t table(num VARCHAR(100))
insert into @t
select choice FROM QuestionAnswers where QuestionID=2674
select num from @t
declare @s varchar(8000)
select @s = COALESCE(@s + ',', '') + num
from @t
exec('select '+@s)
but it doesn't work.
source to share
This type of data transformation is known as pivot. SQL Server 2005+ has a function that will do this data rotation for you. However, there are many ways that you can accomplish this data transformation.
Here's a query PIVOT
that will work with your example data:
select *
from
(
select id, [option], right([option], 1) col
from yourtable
) src
pivot
(
max([option])
for col in (a, b, c, d)
) piv
See SQL Fiddle with Demo .
This can also be accomplished using an aggregate function with the expression CASE
:
select id,
max(case when col = 'a' then [option] else null end) a,
max(case when col = 'b' then [option] else null end) b,
max(case when col = 'c' then [option] else null end) c,
max(case when col = 'd' then [option] else null end) d
from
(
select id, [option], right([option], 1) col
from yourtable
) src
group by id
See SQL Fiddle with Demo .
You can make multiple joins in your table:
select a.id,
a.[option] a,
b.[option] b,
c.[option] c,
d.[option] d
from yourtable a
left join yourtable b
on a.id = b.id
and right(b.[option], 1) = 'b'
left join yourtable c
on a.id = c.id
and right(c.[option], 1) = 'c'
left join yourtable d
on a.id = d.id
and right(d.[option], 1) = 'd'
where right(a.[option], 1) = 'a'
Finally, this can be done using dynamic sql if the values to be translated into columns are unknown:
DECLARE @colsName AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsName = STUFF((SELECT distinct ', ' + QUOTENAME(right([option], 1)) +' as '+ right([option], 1)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(right([option], 1))
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @colsName + ' from
(
select id, [option], right([option], 1) col
from yourtable
) x
pivot
(
max([option])
for col in (' + @cols + ')
) p '
execute(@query)
The result of all requests is:
| ID | A | B | C | D |
----------------------------------------------
| 1 | optionA | optionB | optionC | optionD |
source to share
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO
Ref http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
Edited
DECLARE @Product TABLE (ID int, _option varchar(10) )
-- Inserting Data into Table
INSERT INTO @Product
(
ID,
_option
)
SELECT 1, 'optionA' UNION ALL
SELECT 1, 'optionB' UNION ALL
SELECT 1, 'optionC' UNION ALL
SELECT 1, 'optionD'
SELECT
optionA, optionB, optionC, optionD
FROM (
SELECT ID, _option
FROM @Product) up
PIVOT (SUM(ID) FOR _option IN (optionA, optionB, optionC, optionD)) AS pvt
source to share