| View previous topic :: View next topic |
| Author |
Message |
Ravi
Beginner
Joined: 27 Jun 2005
Posts: 88
Topics: 2
|
 Posted: Thu Jan 05, 2006 9:59 am Post subject: Query for Row into Cols |
 |
|
I need a query which will return rows into columns.
I tried this approach but the problem with it is
| Code: | select * from #temp123
col
11
22
33
44
55
66
77
88
select
(select col from #temp123 root where 0=(select count(1) from #temp123 where root.col> col)) as col1,
(select col from #temp123 root where 1=(select count(1) from #temp123 where root.col> col)) as col2,
(select col from #temp123 root where 2=(select count(1) from #temp123 where root.col> col)) as col3,
(select col from #temp123 root where 3=(select count(1) from #temp123 where root.col> col)) as col4,
(select col from #temp123 root where 4=(select count(1) from #temp123 where root.col> col)) as col5,
(select col from #temp123 root where 5=(select count(1) from #temp123 where root.col> col)) as col6,
(select col from #temp123 root where 6=(select count(1) from #temp123 where root.col> col)) as col7,
(select col from #temp123 root where 7=(select count(1) from #temp123 where root.col> col)) as col8
col1 col2 col3 col4 col5 col6 col7 col8
11 22 33 44 55 66 77 88
|
Due to the subquery limitation which is 16 if there are more we cannot do it.
The max# of rows I will be having is 10. So that has to be converted into 10 columns.
I did a search but didn't  find a similar post. I remember once it was done thru a SORT step. But I need thru a query.
Cheers |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
Ravi
Beginner
Joined: 27 Jun 2005
Posts: 88
Topics: 2
|
| Posted: Thu Jan 05, 2006 10:46 am Post subject: |
|
|
| Code: |
SELECT KEY
,SUM(CASE WHEN MON = 'JAN' THEN QTY ELSE 0 END) AS JAN
,SUM(CASE WHEN MON = 'FEB' THEN QTY ELSE 0 END) AS FEB
,SUM(CASE WHEN MON = 'MAR' THEN QTY ELSE 0 END) AS MAR
,SUM(CASE WHEN MON = 'APR' THEN QTY ELSE 0 END) AS APR
FROM TABLE
GROUP BY KEY |
It has constants. JAN, FEB or 1,2,3.. I don't know the values in this case. That made it a bit complex for me. 
Its like parent child.
Given the parent, all the childs has to be in a single row. Current
| Code: |
select child from ChildTable where parent = parent1
Output:
child
-----
child1
child2
child3
...
...
select child from ChildTable where parent = parent3
child
-----
childx
I want to generate something like below
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10
------- ------ ------ ------ ------ ------ ------ ------ ------ ------
parent1 child1 child2 child3
parent2 childa childb childc
parent3 childx
parent4
parent5 child4 child5 child6 child7 child8 child9
parent6 child~ child! child@ child# child$ child% child^ child& child* |
The worst case scenario i thought is using a cursor append all the childs into a single column. But this involves two cursors, First parent has to be retreived and then corresponding childs, appended in each fetch.
| Code: | col1 col2
------- --------------------------------------------------
parent1 child1 child2 child3
parent2 childa childb childc
parent3 childx
parent4
parent5 child4 child5 child6 child7 child8 child9
parent6 child~ child! child@ child# child$ child% child^ child& child* |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 05, 2006 11:02 am Post subject: |
|
|
Ravi,
What is the format of the child ? is it numeric or character?
If I understand correctly you have the data as follows and the max occurance for a parent is 10 . is that right?
| Code: |
---------+---------+---------
PARENT CHILD
---------+---------+---------
PARENT00
PARENT01 CHILD01
PARENT02 CHILD01
PARENT02 CHILD02
PARENT03 CHILD01
PARENT03 CHILD02
PARENT03 CHILD03
PARENT04 CHILD01
PARENT04 CHILD02
PARENT04 CHILD03
PARENT04 CHILD04
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Ravi
Beginner
Joined: 27 Jun 2005
Posts: 88
Topics: 2
|
| Posted: Thu Jan 05, 2006 11:06 am Post subject: |
|
|
| kolusu wrote: |
What is the format of the child ? is it numeric or character?
Numeric
If I understand correctly you have the data as follows and the max occurance for a parent is 10 . is that right? Yes
|
| Code: | ---------+---------+---------
PARENT CHILD
---------+---------+---------
PARENT00
PARENT01 CHILD10
PARENT02 CHILD21
PARENT02 CHILD22
PARENT03 CHILD31
PARENT03 CHILD32
PARENT03 CHILD33
PARENT04 CHILD41
PARENT04 CHILD42
PARENT04 CHILD43
PARENT04 CHILD44 |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 05, 2006 12:03 pm Post subject: |
|
|
| Quote: |
It has constants. JAN, FEB or 1,2,3.. I don't know the values in this case. That made it a bit complex for me.
|
Ravi,
We create the seqnum and then use to populate the columns. Try this sql. Did not check for performance 
| Code: |
SELECT C.PARENT
,SUM(CASE WHEN C.SEQ = 1 THEN C.CHILD ELSE 0 END) AS COL01
,SUM(CASE WHEN C.SEQ = 2 THEN C.CHILD ELSE 0 END) AS COL02
,SUM(CASE WHEN C.SEQ = 3 THEN C.CHILD ELSE 0 END) AS COL03
,SUM(CASE WHEN C.SEQ = 4 THEN C.CHILD ELSE 0 END) AS COL04
,SUM(CASE WHEN C.SEQ = 5 THEN C.CHILD ELSE 0 END) AS COL05
,SUM(CASE WHEN C.SEQ = 6 THEN C.CHILD ELSE 0 END) AS COL06
,SUM(CASE WHEN C.SEQ = 7 THEN C.CHILD ELSE 0 END) AS COL07
,SUM(CASE WHEN C.SEQ = 8 THEN C.CHILD ELSE 0 END) AS COL08
,SUM(CASE WHEN C.SEQ = 9 THEN C.CHILD ELSE 0 END) AS COL09
,SUM(CASE WHEN C.SEQ = 10 THEN C.CHILD ELSE 0 END) AS COL010
FROM (SELECT SUM(SEQ) SEQ
,B.PARENT
,B.CHILD
FROM (SELECT PARENT
,CHILD
,INT(1) AS SEQ
FROM Child_table) A
,Child_table B
WHERE (A.PARENT = B.PARENT
AND A.CHILD <= B.CHILD)
GROUP BY B.PARENT
,B.CHILD) C
GROUP BY C.PARENT
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Ravi
Beginner
Joined: 27 Jun 2005
Posts: 88
Topics: 2
|
| Posted: Thu Jan 05, 2006 12:17 pm Post subject: |
|
|
Cool solution.. i was thinking how to generate a seq number myself... and this is the way.
Thanks Kolusu.. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
