Getting the details of the rows into columns

Question

Post reply

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: 95405 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:

Requestid RequestorName ApproverName ApprovedDate

1 XYZ AAAAA 2010-10-22

1 XYZ BBBBB 2010-10-23

2 UVW CCCCC 2010-10-24

2 UVW DDDDD 2010-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