I have this kind of table :
Name Date Value
-----------------------
Test 1/1/2001 10
Test 2/1/2001 17
Test 3/1/2001 52
Foo 5/4/2011 15
Foo 6/4/2011 321
My 15/5/2005 36
My 25/7/2005 75
And I would like to show the results like this :
Name Date Value Name Date Value Name Date Value
---------------------------------------------------------------------
Test 1/1/2001 10 Foo 5/4/2011 15 My 15/5/2005 36
Test 2/1/2001 17 Foo 6/4/2011 321 My 25/7/2005 75
Test 3/1/2001 52
I need to show as many columns as what is present in my Name column
How could I do this in Sql ?
In order to get the result that you want, you are going to have to unpivot the columns in your table and apply the pivot function.
The unpivot can be done using either the UNPIVOT function or you can use CROSS APPLY with VALUES.
UNPIVOT:
select rn,
col +'_'+cast(dr as varchar(10)) col,
new_values
from
(
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
) d
unpivot
(
new_values
for col in (name, date, value)
) un;
CROSS APPLY:
select rn,
col +'_'+cast(dr as varchar(10)) col,
c.value
from
(
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
) d
cross apply
(
values
('Name', name), ('Date', date), ('Value', Value)
) c (col, value);
See SQL Fiddle with Demo of both versions. This gives the result:
| RN | COL | NEW_VALUES |
-----------------------------
| 1 | name_1 | Foo |
| 1 | date_1 | 04/05/2011 |
| 1 | value_1 | 15 |
| 2 | name_1 | Foo |
| 2 | date_1 | 04/06/2011 |
| 2 | value_1 | 321 |
| 1 | name_2 | My |
| 1 | date_2 | 05/15/2005 |
| 1 | value_2 | 36 |
These queries take your existing columns values and converts them to rows. Once they are in rows, you create the new column names by using the windowing function dense_rank.
Once the data has been converted to rows, you then use the new column names (created with the dense_rank value) and apply the PIVOT function.
PIVOT with UNPIVOT:
select name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3
from
(
select rn,
col +'_'+cast(dr as varchar(10)) col,
new_values
from
(
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
) d
unpivot
(
new_values
for col in (name, date, value)
) un
) src
pivot
(
max(new_values)
for col in (name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3)
) piv;
PIVOT with CROSS APPLY:
select name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3
from
(
select rn,
col +'_'+cast(dr as varchar(10)) col,
c.value
from
(
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
) d
cross apply
(
values
('Name', name), ('Date', date), ('Value', Value)
) c (col, value)
) src
pivot
(
max(value)
for col in (name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3)
) piv;
See SQL Fiddle with Demo.
Dyanmic PIVOT:
The above versions will work great if you have a limited or known number of columns, if not, then you will need to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col +'_'+cast(dr as varchar(10)))
from
(
select dense_rank() over(order by name) dr
from yourtable
) t
cross apply
(
values(1, 'Name'), (2, 'Date'), (3, 'Value')
) c (sort, col)
group by col, dr, sort
order by dr, sort
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
from
(
select rn,
col +''_''+cast(dr as varchar(10)) col,
c.value
from
(
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
) d
cross apply
(
values
(''Name'', name), (''Date'', date), (''Value'', Value)
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p'
execute(@query)
See SQL Fiddle with Demo.
The result for each of the queries is:
| NAME_1 | DATE_1 | VALUE_1 | NAME_2 | DATE_2 | VALUE_2 | NAME_3 | DATE_3 | VALUE_3 |
-------------------------------------------------------------------------------------------------
| Foo | 04/05/2011 | 15 | My | 05/15/2005 | 36 | Test | 01/01/2001 | 10 |
| Foo | 04/06/2011 | 321 | My | 07/25/2005 | 75 | Test | 01/02/2001 | 17 |
| (null) | (null) | (null) | (null) | (null) | (null) | Test | 01/03/2001 | 52 |