Crosstab in SQL Server 2000

[ColdWaterBlue]

I am trying to execute a crosstab query in SQL Server 2000, but alot of the columns display null values for every row. This is an example of what I am getting: Test | Prompt | 0412-0001 | 0412-0002 | 0412-0003 ------------------------------------------------------- BTEX | Benzene | NULL | NULL | ND ph VALUE | ph VALUE | NULL | NULL | 7 And what I want displayed is the following: Test | Prompt | 0412-0003 ------------------------------- BTEX | Benzene | ND ph VALUE | ph VALUE | 7 (I think the formatting is screwing up with the columns, but | indicates a column separator) I am using the stored procedure found here: http://www.sqlteam.com/item.asp?itemID=2955[^] I do not know much about SQL programming and I was wondering if there is a way to modify this stored procedure to eliminate all columns that contain no data. Nick

[Colin Angus Mackay]

Gosh! I could not even follow that. I'd have to sit down with paper and pencil to work that out. Here is an example of doing a cross tab query from the SQL Server books on-line: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp[^] I'm guessing that both (the query you linked to and the one I've linked to) probably suffer from the same weakness. If there is a null in the source data then the value in the pivot table will be null also. I don't know if that helps any - but the Microsoft example does at least show how to do a simple cross-tab query, even although it isn't a catch all like the one you linked to. I suggest starting with the Microsoft example and working from there.

---

Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums