Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
jmiskey

jmiskey

Programmer
Dec 17, 2003
80
US
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:
Code: 
ID   Pay_Freq
ABC    A
MMM    S
XYZ    A
XYZ    B
ABC    W
XYZ    W

And here is what I want to get to:
Code: 
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.
 
Sort by date Sort by votes
Try this...

Code: 
[COLOR=blue]Select[/color] Id,
       [COLOR=#FF00FF]Max[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Pay_Freq = [COLOR=red]'A'[/color] [COLOR=blue]Then[/color] [COLOR=red]'A'[/color] [COLOR=blue]Else[/color] [COLOR=red]''[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] PayA,
       [COLOR=#FF00FF]Max[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Pay_Freq = [COLOR=red]'S'[/color] [COLOR=blue]Then[/color] [COLOR=red]'S'[/color] [COLOR=blue]Else[/color] [COLOR=red]''[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] PayS,
       [COLOR=#FF00FF]Max[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Pay_Freq = [COLOR=red]'B'[/color] [COLOR=blue]Then[/color] [COLOR=red]'B'[/color] [COLOR=blue]Else[/color] [COLOR=red]''[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] PayB,
       [COLOR=#FF00FF]Max[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Pay_Freq = [COLOR=red]'W'[/color] [COLOR=blue]Then[/color] [COLOR=red]'W'[/color] [COLOR=blue]Else[/color] [COLOR=red]''[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] PayW
[COLOR=blue]From[/color]   [!]TableName[/!]
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Thanks for the response. There are just two potential issues:

1. We may not know all the payroll frequencies codes ahead of time. For example, we have some companies that have multiple bi-weekly pay frequencies, so their codes are "B1" and "B2". Ideally, we would like to write the query without having to know all the possible options ahead of time.

2. Ideally, we would like to have the results of the resulting "left-justified" (for lack of a better term). What I mean, is we would like to have no "blank" gaps in between the values moving from left-to-right, i.e. we want this:
Code: 
ID    Pay1   Pay2   Pay3
ABC   A       W
MMM   S
XYZ   A       B      W
instead of this:
Code: 
ID    Pay1   Pay2   Pay3   Pay4
ABC   A                     W
MMM           S
XYZ   A              B      W
I know it would be fairly easy if the original table has an Pay ID field, i.e.
Code: 
ID   Pay_ID   Pay_Freq
ABC    1       A
ABC    2       W
MMM    1       S
XYZ    1       A
XYZ    2       B
XYZ    3       W
Unfortunately, we don't have any control over the table design and this is what we have to work with.

Can it be done, or am I asking for too much?

 
Upvote 0 Downvote
Would a comma delimited list of paycodes work for you?

[tt]
ID PayCodes
ABC A,W
MMM S
XYZ A,B,W
[/tt]

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

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hmmm...

That is something to ponder. I will have to re-consider my report to see if that is do-able. We had intended to show different fields, but may be able to change it.
 
Upvote 0 Downvote
I think the biggest challenge here would be to dynamically bump the data from one column to another. While I'm sure this is possible, it would probably involve dynamic sql and a cursor/while loop. For various reasons, this would not be very 'performance friendly'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Yes, I thought that might be the case.

If I were doing on Microsoft Access, I would probably create a make-table query to create a temporary table that would create that Pay_ID field via some VBA code. However, I don't think that approach is too feasible here.

It's hard to overcome poor table design...

Thanks for the ideas, though. Hopefully I can get your last solution to work.
 
Upvote 0 Downvote
jmiskey -

I wrote something just like this for a friend last week, and with just a couple changes was able to get it to work for your needs. It was quite the headache, so maybe it will save you the same...

I am sure this could stand some optimization, and only reason I put the results into ##T2 rather than just executing the select was in case you needed to do anything with the table afterwards. Only problem is, it returns a column for each pay_freq (I assume A-Z?), but maybe this is not a problem for you.

One note DO NOT LET IT DROP YOUR REAL TABLE. I am sure you know this, but I get nervous when posting anything with DROP commands for test data, sorry...

Anyway, hope it helps,

Alex

Code: 
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (ID [COLOR=blue]varchar[/color](3), Pay_Freq [COLOR=blue]varchar[/color](1))

[COLOR=blue]insert[/color] #t
[COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'MMM'[/color], [COLOR=red]'S'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'B'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'DEF'[/color], [COLOR=red]'C'[/color]




[COLOR=blue]declare[/color] @strsql [COLOR=blue]varchar[/color](8000)
[COLOR=blue]declare[/color] @strsql2 [COLOR=blue]varchar[/color](8000)
[COLOR=blue]declare[/color] @i [COLOR=blue]int[/color], @i2 [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 0
[COLOR=blue]set[/color] @i2 = 65
[COLOR=green]/* 65 = ascii value for 'A' */[/color]

[COLOR=blue]set[/color] @strsql = [COLOR=red]'select distinct a.ID '[/color]
[COLOR=blue]set[/color] @strsql2 = [COLOR=red]' from #t a '[/color]


[COLOR=blue]while[/color] @i < 26
	[COLOR=blue]begin[/color]
	[COLOR=blue]set[/color] @strsql = @strsql +[COLOR=red]', '[/color] + [COLOR=blue]char[/color](@i2)+[COLOR=#FF00FF]cast[/color](@i [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]'.Pay_Freq as PF'[/color] 
		+ [COLOR=blue]char[/color](@i2) + [COLOR=red]' '[/color]
	[COLOR=blue]set[/color] @strsql2 = @strsql2 + [COLOR=red]'left join (select ID, Pay_Freq from #t where Pay_Freq = '''[/color] 
		+ [COLOR=blue]char[/color](@i2) + [COLOR=red]''') '[/color] + [COLOR=blue]char[/color](@i2)+[COLOR=#FF00FF]cast[/color](@i [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]' on a.ID = '[/color] 
		+ [COLOR=blue]char[/color](@i2)+[COLOR=#FF00FF]cast[/color](@i [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]'.ID '[/color]
	[COLOR=blue]set[/color] @i = @i + 1
	[COLOR=blue]set[/color] @i2 = @i2 + 1
[COLOR=blue]end[/color]


[COLOR=blue]execute[/color] (@strsql +[COLOR=red]' into ##t2 '[/color] + @strsql2)

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] ##t2

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] ##t2

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
D'oh, just realized that it wouldn't work as written, because you don't want the column for each individual pay_freq... [dazed]

I'm going to try and modify the one from last week, sorry about taht

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
That looks a bit complex, and those table drops do scare me.

Anyway, it looks like it would run in to the same problems I mentioned to George six posts up, where the number of possible values is unknown (not pre-determined) and we do all the results to be "left-justified" (instead of having a field for each possible field, we want something like "1st pay freq", "2nd pay freq", etc.)
 
Upvote 0 Downvote
They are not too scary, just be sure not to drop your production table :) And as far as the complexity is concerned, believe me that I looked far and wide and could not find a simpler way to do it.

Anyway, here is the rigth one (actually closer to my original than what I posted before). This one certainly does work for variable numbers of columns (up to 25, but this is somewhat easily modified)

Code: 
[COLOR=green]/* create test data */[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (nID [COLOR=blue]int[/color] null, ID [COLOR=blue]varchar[/color](3), pay_freq [COLOR=blue]varchar[/color](1))
[COLOR=blue]insert[/color] #t (ID, pay_freq)
[COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'MMM'[/color], [COLOR=red]'S'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'B'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'DEF'[/color], [COLOR=red]'C'[/color]

[COLOR=green]/* create variable for different row values (yr, account #, etc...) */[/color]
[COLOR=blue]declare[/color] @cnt [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @cnt = 1

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #tloop (nID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), ID [COLOR=blue]varchar[/color](3))
[COLOR=blue]insert[/color] #tloop
[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] ID [COLOR=blue]from[/color] #t

[COLOR=blue]while[/color] @cnt <= ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](nID) [COLOR=blue]from[/color] #tloop)

[COLOR=blue]begin[/color]
	[COLOR=green]--this piece assigns 'ID numbers' that reset each year, 
[/color]	[COLOR=green]--to be used in ordering later result set
[/color]
	[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t2 (nID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), ID [COLOR=blue]varchar[/color](3), pay_freq [COLOR=blue]varchar[/color](1))

	[COLOR=blue]insert[/color] #t2 
	[COLOR=blue]select[/color] ID, pay_freq
	[COLOR=blue]from[/color] #t
	[COLOR=blue]where[/color] ID = ([COLOR=blue]select[/color] ID [COLOR=blue]from[/color] #tloop [COLOR=blue]where[/color] nID = @cnt)

	[COLOR=green]--select * from #t2
[/color]
	[COLOR=blue]update[/color] a
	[COLOR=blue]set[/color] a.nID = b.nID
	[COLOR=blue]from[/color] #t a
	[COLOR=blue]inner[/color] [COLOR=blue]join[/color] #t2 b
	[COLOR=blue]on[/color] a.ID = b.ID
	and a.pay_freq = b.pay_freq

	[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t2
	
	[COLOR=blue]set[/color] @cnt = @cnt + 1

[COLOR=blue]end[/color]

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #tloop


[COLOR=blue]declare[/color] @strsql [COLOR=blue]varchar[/color](1000)
[COLOR=blue]declare[/color] @strsql2 [COLOR=blue]varchar[/color](1000)
[COLOR=blue]declare[/color] @i [COLOR=blue]int[/color], @i2 [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 0
[COLOR=blue]set[/color] @i2 = 66
[COLOR=green]/* 66 = ascii value for 'B' */[/color]

[COLOR=blue]set[/color] @strsql = [COLOR=red]'select distinct a.ID '[/color]
[COLOR=blue]set[/color] @strsql2 = [COLOR=red]' from #t a '[/color]

[COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](nID) [COLOR=blue]from[/color] #t)
	[COLOR=blue]begin[/color]
	[COLOR=blue]set[/color] @strsql = @strsql +[COLOR=red]', '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]'.Pay_Freq as PF'[/color] + [COLOR=#FF00FF]cast[/color](@i + 1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]' '[/color]
	[COLOR=blue]set[/color] @strsql2 = @strsql2 + [COLOR=red]'left join (select ID, Pay_Freq from #t where nID = '[/color] 
	+ [COLOR=#FF00FF]cast[/color](@i + 1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]) + [COLOR=red]') '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]' on a.ID = '[/color] + [COLOR=blue]char[/color](@i2) + [COLOR=red]'.ID '[/color]
	[COLOR=blue]set[/color] @i = @i + 1
	[COLOR=blue]set[/color] @i2 = @i2 + 1
[COLOR=blue]end[/color]

[COLOR=green]--print (@strsql + @strsql2)
[/color][COLOR=blue]execute[/color] (@strsql + @strsql2)

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Essentially what I need to build has to be able to be dynamic and built on-the-fly every time a certain Crystal Report is run (the data query I am trying to create is part of the Data Source of my report).

So I don't know how possible/feasible it is to have something that builds temporary tables on the fly, especially when volume could be high at times.

Thoughts?
 
Upvote 0 Downvote
W/o cursors:
Code: 
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t1 (ID [COLOR=blue]varchar[/color](3), Pay_Freq [COLOR=blue]varchar[/color](1))

[COLOR=blue]insert[/color] #t1
[COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'MMM'[/color], [COLOR=red]'S'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'A'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'B'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'ABC'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'XYZ'[/color], [COLOR=red]'W'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'DEF'[/color], [COLOR=red]'C'[/color]




[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] #T1 [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 #t1 GROUP BY Id'[/color]
[COLOR=blue]EXEC[/color] (@cSQL)
[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #t1


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Boris - Nice one! That is going in the vault for sure... I should've asked you last week :)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
It is a compilation from your and George's suggestions and little me :)

P.S. I saw we all use a new goodies - George excellent program for syntax coloring

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Isn't it the best? It makes things look so much nicer, especially for long posts!

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Cool code, but I don't think I will be able to use it for the same reasons I can't use Alex's.
 
Upvote 0 Downvote
You could name the columns PayFreq1 .... PayFreqN, BUT you should add as many columns in report as you thing the maximum they could be.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
jmiskey - If you are unable to use temp tables or table variables, you are going to find this a daunting task indeed! I suggest that you try either query, it will probably be much faster than you expect.

Temp tables and table variables are created and disposed of within the scope of the current query (hope I say this right) so you will not need to worry about concurrency issues if this your concern. I suggest that you try either one before deciding they will not work.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Alex,

You're missing the point.

With my first post, and then yours and boris's, the output looks something like this...

[tt]
Id Pay_1 Pay_2 Pay_3 Pay_4 Pay_5
--- ----- ----- ----- ----- -----
ABC A W
DEF C
MMM S
XYZ A B W
[/tt]

Instead, he wants...

[tt]
Id Pay_1 Pay_2 Pay_3 Pay_4 Pay_5
--- ----- ----- ----- ----- -----
ABC A W
DEF C
MMM S
XYZ A B W
[/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
461
mikrom
mikrom
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
250
bharons
bharons
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
334
BobSuruncle
BobSuruncle
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
229
Mike Lewis
Mike Lewis
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
163
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top