This question already has an answer here:
-
SQL turning values returned in 11 rows into 89 total columns
2 answers
this is my query
select * from dbo.tblHRIS_ChildDetails where intSID=463
output:
intCHID intsid nvrchildname nvrgender dttchildDOB Occupation
3 463 SK Female 2001-12-11 00:00:00.000 Studying
4 463 SM Male 2007-10-08 00:00:00.000 Student
i need the output like this this is query is dynamic it may return n number of rows based on the intSID
chidname1 gender DOB childoccupation1 chidname2 gender DOB childoccupation2
SK female 2001-12-11 00:00:00.000 studying SM Male 2007-10-08 00:00:00.000 Student
For this type of data, you will need to implement both the UNPIVOT and then the PIVOT functions of SQL Server. The UNPIVOT takes your data from the multiple columns and place it into two columns and then you apply the PIVOT to transform the data back into columns.
If you know all of the values that you want to transform, then you can hard-code it, similar to this:
select *
from
(
select value, col+'_'+cast(rn as varchar(10)) col
from
(
select nvrchildname,
nvrgender,
convert(varchar(10), dttchildDOB, 120) dttchildDOB,
occupation,
row_number() over(partition by intsid order by intCHID) rn
from tblHRIS_ChildDetails
where intsid = 463
) src
unpivot
(
value
for col in (nvrchildname, nvrgender, dttchildDOB, occupation)
) unpiv
) src1
pivot
(
max(value)
for col in ([nvrchildname_1], [nvrgender_1],
[dttchildDOB_1], [occupation_1],
[nvrchildname_2], [nvrgender_2],
[dttchildDOB_2], [occupation_2])
) piv
See SQL Fiddle with Demo
Now, if you have an unknown number of values to transform, then you can use dynamic SQL for this:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('tblHRIS_ChildDetails') and
C.name not in ('intCHID', 'intsid')
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(c.name
+'_'+ cast(t.rn as varchar(10)))
from
(
select row_number() over(partition by intsid order by intCHID) rn
from tblHRIS_ChildDetails
) t
cross apply sys.columns as C
where C.object_id = object_id('tblHRIS_ChildDetails') and
C.name not in ('intCHID', 'intsid')
group by c.name, t.rn
order by t.rn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select col+''_''+cast(rn as varchar(10)) col, value
from
(
select nvrchildname,
nvrgender,
convert(varchar(10), dttchildDOB, 120) dttchildDOB,
occupation,
row_number() over(partition by intsid order by intCHID) rn
from tblHRIS_ChildDetails
where intsid = 463
) x
unpivot
(
value
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(value)
for col in ('+ @colspivot +')
) p'
exec(@query)
See SQL Fiddle with Demo
The result of both queries is:
| NVRCHILDNAME_1 | NVRGENDER_1 | DTTCHILDDOB_1 | OCCUPATION_1 | NVRCHILDNAME_2 | NVRGENDER_2 | DTTCHILDDOB_2 | OCCUPATION_2 |
-----------------------------------------------------------------------------------------------------------------------------
| SK | Female | 2001-12-11 | Studying | SM | Male | 2007-10-08 | Student |
You have to provide distinct column names but besides that, it's simple. But as others stated, this is not commonly done and looks like if you want print some report with two columns.
select
max(case when intCHID=1 then nvrchildname end) as chidname1,
max(case when intCHID=1 then gender end) as gender1,
max(case when intCHID=1 then dttchildDOB end) as DOB1,
max(case when intCHID=1 then Occupation end) as cildOccupation1,
max(case when intCHID=2 then nvrchildname end) as chidname2,
max(case when intCHID=2 then gender end) as gender2,
max(case when intCHID=2 then dttchildDOB end) as DOB2,
max(case when intCHID=2 then Occupation end) as cildOccupation2
from
dbo.tblHRIS_ChildDetails
where
intSID=463
