Create Cross-Tab Query From Table with Only Two Fields 1

[jmiskey]

From my experience with Microsoft Access, I know you can create Cross-Tab queries if you have at least 3 fields. However, I am trying to create a Cross-Tab Query in SQL Server when I only have 2 fields. Here is sample of what I have:

ID   Pay_Freq
ABC    A
MMM    S
XYZ    A
XYZ    B
ABC    W
XYZ    W

And here is what I want to get to:

ID    Pay1   Pay2   Pay3
ABC   A       W
MMM   S
XYZ   A       B      W

So essentially, I simply want to show each Company ID record once, and all their different Pay Frequencies listed across in its own field.

How can I accomplish this?

Thanks.

 

[jmiskey]

select 'ABC', 'A'
union all select 'MMM', 'S'
union all select 'XYZ', 'A'
union all select 'XYZ', 'B'
union all select 'ABC', 'W'
union all select 'XYZ', 'W'
union all select 'DEF', 'C'

It looks like in your code, you have to need to know all the companies and pay frequencies ahead of time, and then enter "union all select" statement for each possible combination.

We have over 900 companies and 7 pay frequencies and are adding more every week. If I understand it correctly, I would need to create over 6,300 lines to get this to work!

The ability to make it dynamic is essential.

 

[bborissov]

jmiskey,
That is just preparation for the test, you don't need that!
You must use your actual table name instead of #t1 (or #t) JUST DON'T DROP IT!!!
So your actual code should look like this (change everywhere in that query YourTableName with the actual name of the table)

[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (PayFreq [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] Pay_Freq [COLOR=blue]FROM[/color] YourTableName [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Pay_Freq
[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @Temp

[COLOR=blue]declare[/color] @cSQL [COLOR=blue]varchar[/color](8000)
[COLOR=blue]declare[/color] @cPay [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](20)

[COLOR=blue]SELECT[/color] @cPay = [COLOR=#FF00FF]MIN[/color](PayFreq) [COLOR=blue]FROM[/color] @Temp
[COLOR=blue]SET[/color] @cSQL = [COLOR=red]'SELECT Id'[/color]
[COLOR=blue]WHILE[/color] @cPay [COLOR=blue]IS[/color] NOT NULL
      [COLOR=blue]BEGIN[/color]
         [COLOR=blue]SET[/color] @cSQL = @cSQL + [COLOR=red]', MAX( CASE WHEN Pay_Freq = '''[/color] + @cPay+[COLOR=red]''' THEN '''[/color]+@cPay+[COLOR=red]''' ELSE '''' END) AS PayFreq_'[/color]+@cPay
         [COLOR=blue]SELECT[/color] @cPay = [COLOR=#FF00FF]MIN[/color](PayFreq) [COLOR=blue]FROM[/color] @Temp [COLOR=blue]WHERE[/color] PayFreq > @cPay
      [COLOR=blue]END[/color]

[COLOR=blue]SET[/color] @cSQL = @cSQL + [COLOR=red]' FROM YourTableName GROUP BY Id'[/color]
[COLOR=blue]EXEC[/color] (@cSQL)

George, I'm thinking I'm thinking...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Ah, that. The second query I posted should supply the results in the desired format (unless I have some wacky setting turned on here?)

It just won't supply a fixed number of columns.

Ignorance of certain subjects is a great part of wisdom

 

[jmiskey]

Would a comma delimited list of paycodes work for you?

ID PayCodes
ABC A,W
MMM S
XYZ A,B,W

If so, take a look at this thread: thread183-1159740

George,
I am intrigued by this solution, but can't quite figure out how to make it work in my example. Can you use recursive calculations in a query? Basically, I am trying to create a subquery that will be part of a larger query.

 

[gmmastros]

Basically, my suggestion is this...

Create a function that will return a comma delimited list of pay codes based on the ID you pass to it. Then, you can use the function within a larger query to include the comma delimited list of pay codes.

First, create this function:

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] GetPayCodesForId
	(@ID [COLOR=blue]varchar[/color](20))
Returns [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]AS[/color]
[COLOR=blue]Begin[/color]
    [COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](8000)
    [COLOR=blue]Set[/color] @Output = [COLOR=red]''[/color]

    [COLOR=blue]Select[/color] @Output = @Output + Pay_Freq + ','
    [COLOR=blue]From[/color]   [!]TableName[/!]
    [COLOR=blue]Where[/color]  Id = @ID
           And Pay_Freq [COLOR=blue]Is[/color] Not NULL

    [COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@Output, 1) = [COLOR=red]','[/color]
        [COLOR=blue]Set[/color] @Output = [COLOR=#FF00FF]Left[/color](@Output, Len(@Output) - 1)

    [COLOR=blue]Return[/color] @Output
[COLOR=blue]End[/color]

Then, to use it...

Select Col1, 
       Col2, 
       [!]dbo.GetPayCodesForId(Id) As PayCodes[/!]
from   Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom