PIVOT Table in SQL 2000 with unknown columns

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

Transact-SQL (2000)

PIVOT Table in SQL 2000 with unknown columns

Author

Topic

Gr1m
Starting Member

4 Posts

Posted - 2008-02-11 : 05:19:59

Hi guys,

I am looking for something that functions like the PIVOT table in SQL 2005 (the server here runs 2000 :( ), but I do not find any useful code for a table where the resulting column names are not known.

I have the following table, Feedback:

RegistrationID FieldName Field Value
1 chkReceiveInfo True
1 dropRating 5
1 txtComment Very good
2 chkReceiveInfo False
2 dropRating 7
3 chkReceiveInfo False

etc.

Now I need to be able to display the FieldNames as columns for every RegistrationID with Field Value as the values.

In other words:
RegistrationID chkReceiveInfo dropRating txtComment
1 True 5 Very good
2 False 7 NULL
3 False NULL NULL

It would be even better (but not vital) if the columns could be assigned a type (chk* = bool; drop* = int; txt* = varchar).

Can anybody help me with some clues? I have searched for good examples but nothing has gotten me right yet.

Any help please guys?

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 05:24:30

must help you to get started on this:-

http://www.mssqltips.com/tip.asp?tip=937

Gr1m
Starting Member

4 Posts

Posted - 2008-02-11 : 05:59:53

Im trying that stored proc recommended in the first article. Something along the following lines:
EXECUTE sp_CrossTab
'SELECT * FROM Feedback',
NULL,
NULL,
'FieldName',
'FieldValue',
NULL
But yet gives me only '50000' as a result?? :\

Gr1m
Starting Member

4 Posts

Posted - 2008-02-11 : 06:47:01

Ok well if I execute that stored proc as

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_Crosstab]
@DBFetch = N'Select * FROM Feedback',
@DBWhere = NULL,
@DBPivot = N'SELECT DISTINCT FieldName FROM Feedback',
@DBField = N'FieldName',
@PCField = N'FieldValue',
@PCBuild = N'MAX',
@DBTable = 'Temp'

SELECT 'Return Value' = @return_value

It returns the data right, but every value is in its own row, eg:

RegistrationID Column1 Column2 etc
1 Value
1 Value
2 Value
2 Value

Now I can fix this by grouping the output table, but there must surely be a way to get it formatted before being output?

Because the result needs to be completely dynamic, no fixed structure etc.

Gr1m
Starting Member

4 Posts

Posted - 2008-02-11 : 07:04:04

Ok got it sorted. Dang datestamp and unique ID was making it split the rows *duh*.

Thanks for the help, was very useful.

For any future people who might find it useful:


DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_Crosstab]
		@DBFetch = N'Select RegistrationID, FieldName, FieldValue FROM Feedback',
		@DBWhere = N'FieldName <> ^MailSent^',
		@DBPivot = N'SELECT DISTINCT FieldName FROM Feedback',
		@DBField = N'FieldName',
		@PCField = N'FieldValue',
		@PCBuild = N'MAX'


SELECT	'Return Value' = @return_value

when using the sp in the first article in the link posted by visakh will dynamically convert a vertical table into rows ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources