Crosstab help

Hello,

I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...

I have a table with 4 columns

UNIQUE | Code | FieldID | CustomField

The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.

My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.

Currently my SQL is:

SELECT

[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]

FROM

dbo.[Customer Custom Field]

WHERE

(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code = '07-8111')AND ([Field ID] = 8)

However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.

My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.

Is there something i am missing here? Can someone point me in the right direction?

Thanks in advance!

phorest

It sounds like you need a pivot table. Here is a great article on building queries using dynamic pivot tables.

http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

If you have trouble, provide an example table full of data and I will take a shot at building a sample query.

Good luck!

Larry

|||

Thanks for your help.

Just as I expected, though I did not mention that the database is SQL2000 so the pivot is probably not an option. I suppose I need to use CAST in the SQL statement to accomplish this. Is it possible to do this simply without having to create a stored procedure -or- temporary table?

My sample data below: (NOTE: I am only using the FieldID numbers 6,7, & 8 as they are the only ones I need)

Unique ID Code FieldID Custom Field
-- -- --
5625 05-4256 8 C
5629 COLE0001 6 02/24/2006
5630 COLE0001 7 Need Invoice date & claim#
5631 COLE0001 8 C
5637 05-3945 6 09/07/2006
5638 05-3945 7 Auth expired 08/21/06 (11/18)
5660 05-3670 6 06/17/2006
5661 05-3670 7 auth expired 06/06/06 (3/10)
5670 HUNTER0000 6 04/27/2006
5671 HUNTER0000 7 Need Charges04/07/06 DOS (box checked: not listed on walkout)
5672 HUNTER0000 8 R
5680 REAVES0000 6 04/27/2006
5681 REAVES0000 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5682 REAVES0000 8 R
5684 SMITH0003 6 04/27/2006
5685 SMITH0003 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5686 SMITH0003 8 R

(602 row(s) affected)

|||

I failed to mention that the article above shows both the 2000 and 2005 syntax to accomplish the goal. Let me give it a shot with the data you provided:

Code Snippet

SELECT
DISTINCT T.CODE,
Eight.[Custom Field] as Eight,
Seven.[Custom Field] as Seven,
Six.[Custom Field] as Six
FROM
TEST t
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 8) Eight ON t.Code = Eight.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 7) Seven ON t.Code = Seven.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 6) Six ON t.Code = Six.Code

where TEST is the name of the table I created to hold your data. It produces the following output:

CODE Eight Seven Six
-
05-3670 NULL auth expired 06/06/06 (3/10) 06/17/2006
05-3945 NULL Auth expired 08/21/06 (11/18) 09/07/2006
05-4256 C NULL NULL
COLE0001 C Need Invoice date & claim# 02/24/2006
HUNTER0000 R NULL 04/27/2006
REAVES0000 R NULL 04/27/2006
SMITH0003 R NULL 04/27/2006

(7 row(s) affected)

Please understand that I am not claiming this to be the best/most efficient way of handling your needs. My intuition tells me that there is a clever way to do this that I am overlooking.

Larry

|||

Yes, that works marvelously!

Just for my learning benefit, are the following statements essentially true?

The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?

"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?

SELECT DISTINCT t.Code, Six.[Custom Field] AS Six, Seven.[Custom Field] AS Seven, Eight.[Custom Field] AS Eight

FROM dbo.[Customer Custom Field] AS t

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field]

WHERE ([Field ID] = 6)) AS Six ON t.Code = Six.Code

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field] AS [Customer Custom Field_2]

WHERE ([Field ID] = 7)) AS Seven ON t.Code = Seven.Code

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field] AS [Customer Custom Field_1]

WHERE ([Field ID] = 8)) AS Eight ON t.Code = Eight.Code

WHERE (Six.[Custom Field] BETWEEN '04/01/2007' AND '07/15/2007')

When I constructed the query from your generous guide and tested it, It pulled the data the way I need it. Then I opened the SQL in the design editor and applied the daterange filter and it worked beautifully...

Thank you ever so much!

|||

The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?

Yes. It is setting up an alias. I would normally use something more descriptive or identifiable (ccf comes to mind) but there is only the one table so I got lazy.

"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?

Not exactly. What we are doing is using the same table four times. Remove the distinct and put an * for the fields and you will see what I mean. We are joining the table to itself. The 'Left Outer' part says that all of the rows from the table to the left are to be in the resulting table, even if there isn't a corresponding row in the table on the right. I would suggest looking at Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan if you want to learn more about building complex queries.

You are very welcome. I am glad it worked out so well.

Larry