I have a table like
ID option
1 optionA
1 optionB
1 optionC
1 optionD
And I want a result like:
ID A B C D
1 optionA optionB optionC optionD
What is the best way to do this?
Query which i have tried is
select * from TableName PIVOT (option for ID = 2674 )) as abc
this will not work since PIVOT expects aggregated function..
I have also tried COALESCE like this
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 this doesn't work as well..
This type of data transformation is known as a pivot. In SQL Server 2005+ there is a function that will perform this data rotation for you. However, there are many ways that you can perform this data transformation.
Here is a PIVOT query that will work with your sample 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 performed using an aggregate function with a CASE expression:
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 perform multiple joins on 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'
See SQL Fiddle with Demo
Lastly, this can be done using dynamic sql if the values to be turned into columns is 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)
See SQL Fiddle with Demo
The result of all queries is:
| ID | A | B | C | D |
----------------------------------------------
| 1 | optionA | optionB | optionC | optionD |
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
