Rows to Column Names

[SQLJunkie]

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!

[Stu]

Re: Rows to Column Names

Pivot queries are difficult, but not impossible to build in SQL Server;
here's my stab at it:

DECLARE @SQL varchar(8000)
SET @SQL = 'SELECT MerchID, '

DECLARE @ItemName varchar(50)

DECLARE C CURSOR LOCAL FAST_FORWARD
FOR SELECT DISTINCT ItemName
FROM TranDetail
ORDER BY ItemName

OPEN C

FETCH NEXT FROM C INTO @ItemName

SET @SQL = 'SELECT MerchID, '

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = @SQL + @ItemName +'=SUM(CASE WHEN
ItemName='''+@I temName+''' THEN Price END), '

FETCH NEXT FROM C INTO @ItemName
END

SET @SQL = LEFT(@SQL, LEN(@SQL)-1)
SET @SQL = @SQL + ' FROM TranDetail GROUP BY MerchID'

CLOSE C

DEALLOCATE C

EXEC (@SQL)


I basically build a dynamic SQL statement using a cursor (yes, I know,
cursors are bad, but they can be useful). However, there is a
limitation; the total constructed SQL statement can only be 8000
characters. If your ItemName is long (doesn't appear to be), and you
have a lot of them, the SQL statement itself may fail on you.

HTH,
Stu

[David Portas]

Re: Rows to Column Names

Why not do reports in your reporting app? Almost any of them will do a
crosstab without any programming required.

--
David Portas
SQL Server MVP
--

[Saghir Taj]

Re: Rows to Column Names

Dear

Yeh david is right, if it is possible you must use some reporting tool
which normally do the thing for you! an other option is Microsoft
Excell.

1. Excell Menu > Data > Import External Data.
2. Excell Menu > Date > PivotTable and Pivitchart Wizird.
3. Excell Menu > Help > Microsoft Excell Help or F1 Key :) :) :) :)


Microsoft Excell is best and easiest tool available in market for
ad-hoc Data reporting.

cheers.

Saghir Taj (MCDBA)
www.dbnest.com: The Nest of DB Professionals.
www.Resumedump.com: Career Partner
www.siliconways.net : Design to Suit your IT needs.

[saghir.taj@gmail.com]

Re: Rows to Column Names

Dear

I wrongly spell data as date so you dont need to search for date menu
in excell ... lolzzz.... sorry buddies.

************Exc ell Menu > Data > PivotTable and Pivitchart
Wizird********* **********


Thanks.
Saghir Taj (MCDBA)
www.dbnest.com: The Nest of DB Professionals.
www.Resumedump.com: Career Partner
www.siliconways.net : Design to Suit your IT needs

[SQLJunkie]

Re: Rows to Column Names

Using curosors is fine but total length of my data is way more than
8000 characters. There are more than 150 item names in the real
database and each name is about 20 characters long!

[SQLJunkie]

Re: Rows to Column Names

Thanks for your reply!

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.

Thanks again!

Vishal

[Erland Sommarskog]

Re: Rows to Column Names

SQLJunkie (vsinha73@gmail .com) writes:[color=blue]
> 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.[/color]

If fast execution is critical, you should definitely take the rowset
without thrills from SQL Server, and then transpose it your .Net client.
C#/VB is much better fitted for such operations than SQL Server.

Or, hey, import the data into Excel as is, and have Excel do the
crosstab. A poster here recently claimed that Excel was the best tool
in town for pivots. (I have done pivots myself in Excel though.) Excel
has an OLE interface, but I would expect standard routines being
available to manipulate Excel books from .Net.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp