Newbie Question: Verticle To Horizontal

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

SQL Server Development (2000)

Newbie Question: Verticle To Horizontal

Author

Topic

SlimJim
Starting Member

4 Posts

Posted - 2006-01-06 : 09:14:13

Vertical to Horizontal

Im trying to turn round a table from Vertical to Horizontal.

Im pretty sure im making a BIG hash of it.

Im trying to get FROM:


ID	Group	C	SC	SortOrder
1	1	A	K	1
2	1	B	Null	3
3	1	C	L	4
4	2	D	M	1
5	2	E	N	2
6	3	F	O	1
7	3	G	Null	2
8	3	H	Null	4
9	3	I	P	6
10	3	J	Null	7

TO:

ID	Group	C1	C2	C3	C4 	C5 	C6 	C7
1	1	A	K	B	C	L	Null	Null
2	2	D	M	E	N	Null	Null	Null
3	3	F	O	G	H	I	P	J

Rules:
A maximum combination of 7 Code and Subcode linked to one Group.
Sort order reflects the order the codes are created in.
The sort order will be ascending, but numbers can be missed out.
If a sort number ascending is missed, 1,3,5 either the next C will have data in it, or there will be no more data for that group.
ID’s are just Auto IDs and are not relevant between the two tables.

How do I convert the above table to the table below?

Im currently doing it the LONG way round. i.e.

Step One:
Seven views, all bringing out the Group, code and subcode,
One to show all wherethe sort order is = “1”
Another to show all wherethe sort order is = “2”… etc.

Step Two:
Then a stored procedure creates a #Temp Table from those seven views, left joining on “Group” from view1.
I.E. View1.Group = View2.Group, View1.Group = View3.Group etc


ID	Group	C1	SC1	C2	SC2 	C3 	SC3 	C4 	SC4 	C5 	SC5 	C6 	SC6 	C7 	SC7
1	1	A	K	Null	Null	B	Null	C	L 	Null	Null 	Null	Null 	Null	Null 
2	2	D	M	E	N 	Null	Null	Null 	Null	Null	Null 	Null	Null	Null 	Null
3	3	F	O	G	Null	Null	Null	H	Null	Null	Null	I	P	J	Null

Step Three:
I then update the temp table using:
--C2 Jump two
Update #Temp
SET C2 = C3, SC2 = SC3, C3 = C4, SC3 = SC4, C4 = C5, SC4 = SC5, C5 = C6, SC5 = SC6, C6 = C7, SC6= SC7
WHERE C2 is null
--C3 Jump two
Update #Temp
SET C3 = C4, SC3 = SC4, C4 = C5, SC4 = SC5, C5 = C6, SC5 = SC6, C6 = C7, SC6= SC7
WHERE C3 is null
Etc....
In order to remove all the double nulls.

Step Four:
Then finally update the tables using:
--C1
Update #TempDiags
SET C1 = SC1, SC1 = C2, C2 = SC2, SC2 = C3, C3 = SC3, SC3 = C4, C4 = SC4, SC4 = C5, C5 = SC5, SC5 = C6, C6 = SC6, SC6 = C7, C7 = SC7
WHERE C1 is null

--SC1
Update #TempDiags
SET SC1 = C2, C2 = SC2, SC2 = C3, C3 = SC3, SC3 = C4, C4 = SC4, SC4 = C5, C5 = SC5, SC5 = C6, C6 = SC6, SC6 = C7, C7 = SC7
WHERE SC1 is null

--C2
Update #TempDiags
SET C2 = SC2, SC2 = C3, C3 = SC3, SC3 = C4, C4 = SC4, SC4 = C5, C5 = SC5, SC5 = C6, C6 = SC6, SC6 = C7, C7 = SC7
WHERE C2 is null
Etc....
In order to remove and remaining single nulls.

This seems to me like a VERY long way round …. Is there no easier way? Or a function I can use to do it?

Please post even if this is the best way to do it, just for my piece of mind!

Cheers

SlimJim
Starting Member

4 Posts

Posted - 2006-01-06 : 09:50:45

So is my post too complicated?

Do I need to explain it more?

Someone PLEASE give me SOME feed back!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-06 : 10:10:46

1. people do have day jobs to do 1st to put bread on the table.
2. search here for a sticky FAQ in the New TO SQL section, from Kristen which lists top recurring queries...one (or more) of which deals with rows being turned into columns....pivot style.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-06 : 10:57:40

also do an article search on "cross tab" (be sure to read article comments as well)

Be One with the Optimizer
TG

SlimJim
Starting Member

4 Posts

Posted - 2006-01-06 : 11:00:22

quote:
Originally posted by AndrewMurphy

1. people do have day jobs to do 1st to put bread on the table.

Good point ....Duley noted ... sorry

quote:
Originally posted by AndrewMurphy2. search here for a sticky FAQ in the New TO SQL section, from Kristen which lists top recurring queries...one (or more) of which deals with rows being turned into columns....pivot style.

Problem is that it's not (as far as I know) related to pivoting as the data will still be data, but in a different order, rather then using the data as col names.....

Plus it's the "shuffling" that seems to be quite bulky.

I will wait till people have finished their day jobs and see if anyone has any suggestions

In the mean time I will have a look at the pivot stuff, to be sure that I can't do it that way!

Thanks for the reply Andrew...

SlimJim
Starting Member

4 Posts

Posted - 2006-01-06 : 11:01:06