Rows to Column Names

Question

Rows to Column Names

Vishal Sinha

Ten Centuries

Points: 1174
More actions
June 3, 2005 at 10:49 am

#86896

I am struggling on this issue and was hoping if anyone out there can
help me.
Here is the setup: I have a table with the following data:
TableName: TranDetail
MerchID ItemName Price
------------------
101 A 5
101 B 3.5
101 C 0
102 B 7.6
102 C 4
102 E 65
102 G 4
103 K 35
Table Design
MerchID int
ItemName varchar(50)
Price float
What I would like is a report that looks like this
MerchID A B C E G K
101 5 3.5 0
102 7.6 4 65 4
103 35
This report can change on every run depending on data in table
TranDetail. The column name in report depends on ItemName in table
TranDetail. As seen in the above report, there is no data for Items D,
F, H, I, J and hence they do not show up in the report.
What I need: Code for a stored procedure that can get me this data.
Thanks for your help...
DBA in despair!

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I imagine there is a better way to make a pivot table, but this will work. Hopefully, one of the smarter folks on this site will give you a more elegant answer.

NOTE: I used a #TempTable, so I could simply delete my records as I went along. You may need to handle this a different way. Since I did not see any Primary Key in your description, I could not use that....

CREATE TABLE #TranDetail( MerchID integer,

ItemName varchar(50),

Price float)

INSERT INTO #TranDetail VALUES( 101, 'A', 5)

INSERT INTO #TranDetail VALUES( 101, 'B', 3.5)

INSERT INTO #TranDetail VALUES( 101, 'C', 0)

INSERT INTO #TranDetail VALUES( 102, 'B', 7.6)

INSERT INTO #TranDetail VALUES( 102, 'C', 4)

INSERT INTO #TranDetail VALUES( 102, 'E', 65)

INSERT INTO #TranDetail VALUES( 102, 'G', 4)

INSERT INTO #TranDetail VALUES( 103, 'K', 35)

--------------------------------------------------------------------------------------

DECLARE @sql varchar(200),

@MerchID int,

@LastMerchID int,

@MinItemNames varchar(50),

@MaxItemNames varchar(50),

@ItemNames varchar(50),

@Prices float

SELECT @MerchID = (SELECT MIN( MerchID) FROM #TranDetail)

SELECT @LastMerchID = (SELECT MAX( MerchID) FROM #TranDetail)

--------------------------------------------------------------------------------------

CREATE TABLE #OutPut( MerchID int)

INSERT INTO #OutPut SELECT DISTINCT MerchID FROM #TranDetail

--------------------------------------------------------------------------------------

CREATE TABLE #ItemNames( ItemName varchar(50))

INSERT INTO #ItemNames( ItemName) SELECT DISTINCT ItemName FROM #TranDetail

SELECT @MinItemNames = (SELECT MIN( ItemName) FROM #ItemNames)

SELECT @MaxItemNames = (SELECT MAX( ItemName) FROM #ItemNames)

WHILE @MinItemNames <= @MaxItemNames

BEGIN

SELECT @sql = 'ALTER TABLE #OutPut ADD ' + CONVERT( varchar, @MinItemNames) + ' float NULL'

EXEC( @sql)

DELETE #ItemNames WHERE ItemName = @MinItemNames

SELECT @MinItemNames = (SELECT MIN( ItemName) FROM #ItemNames)

END

--------------------------------------------------------------------------------------

WHILE @MerchID <= @LastMerchID

BEGIN

SELECT @ItemNames = (SELECT TOP 1 ItemName FROM #TranDetail WHERE MerchID = @MerchID)

SELECT @Prices = (SELECT Price FROM #TranDetail WHERE MerchID = @MerchID AND ItemName = @ItemNames)

SELECT @sql = 'UPDATE #OutPut SET ' + RTRIM( LTRIM( @ItemNames)) + ' =' + CONVERT( varchar, @Prices) + ' WHERE MerchID = ' + CONVERT( varchar, @MerchID)

EXEC( @sql)

DELETE #TranDetail

WHERE MerchID = @MerchID

AND ItemName = @ItemNames

AND Price = @Prices

SELECT @MerchID = (SELECT MIN( MerchID) FROM #TranDetail)

END

DROP TABLE #TranDetail

DROP TABLE #ItemNames

SELECT * FROM #OutPut

DROP TABLE #OutPut

I wasn't born stupid - I had to study.

Vishal Sinha Ten Centuries Points: 1174 More actions · Answer 2

Thanks for Your reply,

A few issues though:

1: There are more than 150 item names in the real database and each run can have any number of items selected from 1 to 150 (dynamic list).

2: This is an automated report that runs on a monthly basis so there is no

front end to it. I was hoping to run the SP from a dotnet app and then

export the data to excel and email results. We may also decide to

display the results on a webpage too - so fast execution of the SP is

critical also.

How can I have it so that it

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Sorry, I was gone for a while.

Your number list does not sound too large to need a cursor. I would see how the loop works.

Change these to permenant tables and TRUNCATE them at the head of this code... That will not log the values, so it should not increase the time substantially.

Mess with it..., see what you can make work...

I wasn't born stupid - I had to study.