Getting the details of the rows into columns

Question

Getting the details of the rows into columns

pawan.boyina

SSC-Addicted

Points: 494
More actions
April 20, 2011 at 12:33 am

#235024

There are two table structures which will have the below columns
Table 1 ---- RequestID, Requestorname
Table 2 ---- RequestID,ApproverName,Approved Date
the sample data can be like
Table1
Requestid RequestorName
1 XYZ
Table2
RequestID ApproverName Approved Date
1 AAAAA 22/10/2010
1 BBBBBB 23/10/2010
Here the number of Records in Table2 will be changing for each requestid , Now i want a report of the format
Requestid(Table1), RequestorName(Table1),ApproverName(table2 Row1),Approved date(table2 Row1),ApproverName(table2 Row2) ,Approved date(table2 Row2),.............
ApproverName(table2 RowN) ,Approved date(table2 RowN)
Here we need the report displaying all the requests at a time and the number of rows in table 2 for each request will be changing, Please let me know how to write this query.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

What you are looking for is to PIVOT the rows into columns. Please check out the two articles linked to in my signature dealing with CROSS-TAB or PIVOT tables: Parts 1 and 2.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hunterwood SSCrazy Points: 2074 More actions · Answer 2

Hi Pawan,

There is no way to have SQL Server to return sets with varying number of columns for the different rows.

Normaly you would write a query like:

SELECT T1.Requestid, T1.RequestorName, T2.ApproverName, T2.ApprovedDate

FROM @T1 T1

INNER JOIN @T2 T2 ON T1.RequestID = T2.RequestID

Let's assume we have some data:

DECLARE @T1 as table (RequestID int, Requestorname varchar(100))

DECLARE @T2 as table (RequestID int, ApproverName varchar(100), ApprovedDate datetime)

INSERT INTO @T1 VALUES (1, 'XYZ')

INSERT INTO @T1 VALUES (2, 'UVW')

INSERT INTO @T2 VALUES (1, 'AAAAA', '2010-10-22')

INSERT INTO @T2 VALUES (1, 'BBBBB', '2010-10-23')

INSERT INTO @T2 VALUES (2, 'CCCCC', '2010-10-24')

INSERT INTO @T2 VALUES (2, 'DDDDD', '2010-10-25')

You would then get the result:

RequestidRequestorNameApproverNameApprovedDate

1XYZAAAAA2010-10-22

1XYZBBBBB2010-10-23

2UVWCCCCC2010-10-24

2UVWDDDDD2010-10-25

Then you need to handle this in your calling code and format the data the way you want it.

What you can do is to get a result set with Requestid, RequestorName and a third column with the data from Table 2. The third column could then contain either a concatenated string or xml:

SELECT T1.Requestid, T1.RequestorName,

(SELECT T2.ApproverName + ' ' + CONVERT(CHAR(10), T2.ApprovedDate, 120) + ', '

FROM @T2 T2

WHERE T1.RequestID = T2.RequestID

FOR XML PATH ('')) AS ApproveLog

FROM @T1 T1

(You might also be able so solve your problem with PIVOT, as stated in the other reply...)

/Markus