Creating Headers in SQL Server Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating Headers in SQL Server Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-13 : 08:07:47

writes "I have the database tables similar to the below

Table 1: Elements
=================
Fields Name Data Type
---------------------------------
Parent_Element_Id Integer
Element_Id Integer
Element_Name Varchar

Table 2: Reports
=================
Fields Name Data Type
---------------------------------
Report_ID varChar
Report_Description Varchar
Stores_Id varChar
Month_Start_Date DateTime
Month_End_Date DateTime

Table 3: Reprots_Detail
=======================
Fields Name Data Type
---------------------------------
Report_ID VarChar
Element_ID Integer
Element_Value Double

for Example, I have the values in database like this below...

Table Name: Reports
===================
Report_ID Report_Description Stores_Id Month_Start_Date Month_End_Date
------------------------------------------------------------------------------
R0001 aaaaaaaaaaaaaaaaaa S00001 01-20-2004 02-19-2004
R0002 ssssssssssssssssss S00002 01-20-2004 02-19-2004
R0003 dddddddddddddddddd S00002 02-20-2004 03-19-2004
R0004 ffffffffffffffffff S00001 02-20-2004 03-19-2004
R0005 gggggggggggggggggg S00001 03-20-2004 04-19-2004
R0006 hhhhhhhhhhhhhhhhhh S00003 01-20-2004 02-19-2004

Table Name: Elements
====================
Parament_Element_Id Element_Id Element_Name
----------------------------------------------------
0 1 Anabond
0 2 Bobin
0 3 Coolent
1 11 Anabond 202
1 12 Anabond 101
1 13 Anabond xxx
3 31 Water/Gel Based
3 32 Oil Based
32 321 xxxxxxx
32 322 yyyyyyy
32 323 zzzzzzz

Table Name: Reports_Detail
==========================
Report_Id Element_Id Element_Value
----------------------------------
R0001 321 43
R0001 322 23
R0001 323 54
R0001 11 110
R0004 321 23
R0004 322 55
R0004 11 643
R0004 12 500
......
......
......

my table values are like this.

I want to write the stored Procedure and display the values such that, my SP will not have the headers as column

name of tables. but I want to have like

if Store_Id = S00001 selected, then

my SP should give the the value like

Parent_Element_Id Element_ID Element_name R0001 R0004 R0005
-----------------------------------------------------------------------
0 1 Anabond
0 2 Bobin
0 3 Coolent
1 11 Anabond 202 110 643
1 12 Anabond 101 500
1 13 Anabond xxx
3 31 Water/Gel Based
3 32 Oil Based
32 321 xxxxxxx 43 23
32 322 yyyyyyy 23 55
32 323 zzzzzzz 54
.............
..........
........"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-13 : 09:12:30

do a search for cross tabs in this forum, or pivot tables. check out the Articles at this site as well, Rob's got a "hall of famer" in there that will do this very easily.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources