I am working on a project where I have the below requirement
My source table is
PeriodID PeriodName ProductID ProductName Productvalue
-------- ---------- --------- ----------- ------------
10 Jan 100 A 15
20 Feb 100 A 25
30 Mar 100 A 35
10 Jan 200 B 12
20 Feb 200 B 14
30 Mar 200 B 18
10 Jan 300 C 22
20 Feb 300 C 23
30 Mar 300 C 38
I want the output of the table to be like this
Destination table
PeriodID PeriodName A_ID A B_ID B C_ID C
-------- ---------- ---- -- ---- -- ---- --
10 Jan 100 15 200 12 300 22
20 Feb 100 25 200 14 300 23
30 Mar 100 35 200 18 300 38
I am trying to use pivot and unpivot of sql server 2008, but I am not that familiar with the features of those operators. I know this requirement looks weird,but this is how I want my output result set.
Please help me in this, I am really in trouble from past few days to get over this.
The simplest way to get the result would be to use an aggregate function along with a CASE expression:
select
periodid,
periodname,
max(case when productname = 'A' then productid end) A_ID,
max(case when productname = 'A' then Productvalue end) A,
max(case when productname = 'B' then productid end) B_ID,
max(case when productname = 'B' then Productvalue end) B,
max(case when productname = 'C' then productid end) C_ID,
max(case when productname = 'C' then Productvalue end) C
from yourtable
group by periodid, periodname
order by periodid;
See SQL Fiddle with Demo.
However, if you want to use the PIVOT function to get the result I would first unpivot the productid and productvalue column first, so you no longer have multiple columns but multiple rows of this data. There are several ways that you can unpivot the data, you can use the unpivot function or you can use CROSS APPLY with a UNION ALL or VALUES (if you are using SQL Server 2008+). Since you are using SQL Server 2008 R2, here is how you can use CROSS APPLY with VALUES to unpivot the multiple columns:
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
('_ID', ProductID),
('', Productvalue)
) c (col, value)
See SQL Fiddle with Demo. This gets your data into the format:
| PERIODID | PERIODNAME | COL | VALUE |
|----------|------------|------|-------|
| 10 | Jan | A_ID | 100 |
| 10 | Jan | A | 15 |
| 20 | Feb | A_ID | 100 |
| 20 | Feb | A | 25 |
| 30 | Mar | A_ID | 100 |
Now you can easily apply the PIVOT function to this data:
select periodid, periodname,
A_ID, A, B_ID, B, C_ID, C
from
(
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
('_ID', ProductID),
('', Productvalue)
) c (col, value)
) d
pivot
(
max(value)
for col in (A_ID, A, B_ID, B, C_ID, C)
) piv
order by periodid;
See SQL Fiddle with Demo.
Finally if you have an unknown number of product names, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(productname+col)
from yourtable
cross apply
(
select '_ID', 1 union all
select '', 2
) c (col, so)
group by col, so, productname
order by productname, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT periodid, periodname,' + @cols + '
from
(
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
(''_ID'', ProductID),
('''', Productvalue)
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p
order by periodid'
execute sp_executesql @query;
See SQL Fiddle with Demo. All versions give a result:
| PERIODID | PERIODNAME | A_ID | A | B_ID | B | C_ID | C |
|----------|------------|------|----|------|----|------|----|
| 10 | Jan | 100 | 15 | 200 | 12 | 300 | 22 |
| 20 | Feb | 100 | 25 | 200 | 14 | 300 | 23 |
| 30 | Mar | 100 | 35 | 200 | 18 | 300 | 38 |