One column data to Multiple columns

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 2005 Forums

Transact-SQL (2005)

One column data to Multiple columns

Author

Topic

learntsql

524 Posts

Posted - 2010-05-07 : 03:10:31

Hi All,
How to convert single column data into multiple(specified) number of columns.
for eg;
Col1
----
1
2
3
4
5
6
7
.
.
.

Output should be as
Col1 Col2 Col3
---- ---- ---
1 4 7
2 5
3 6

TIA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-07 : 03:22:22

What are the rules for deciding which numbers go where? Can you provide some real sample data and your expected output?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

learntsql

524 Posts

Posted - 2010-05-07 : 03:28:31

Given data is sample data only.
the rule is in each column should show equal number(percentage) of rows.
for eg; If 3 rows then 33.33% of rows in each column.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-05-07 : 10:12:23

Hi

SELECT T.*
FROM
(
SELECT [COL1]=T1.COL1,[COL2]=T2.COL2,[COL3]=T3.COL3
FROM
#TMP T1
OUTER APPLY
(
SELECT [COL2]=COL1
FROM #TMP T2 WHERE (T1.COL1+3)=T2.Col1
) T2
OUTER APPLY
(
SELECT [COL3]=COL1
FROM #TMP T3 WHERE (T1.COL1+6)=T3.Col1
) T3
) T
WHERE T.COL1 < 4

but i have done hardcoding for 4. its prob there. i will try to come up with better solution

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources