February 24, 2010 at 8:51 pm
Could someone help me with this?
I have data like this:
create table A
(
H int,
D int
)
insert into A
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,2 union all
select 3,3 union all
select 3,4 union all
select 3,5 union all
select 3,6 union all
select 3,7
I wanna create a result like this:
HD1D2D3
112null
21nullnull
3123
3456
37nullnull
First I sort table A by H and D. Then values of column D were divided into 3 columns D1,D2,D3 as above.
The question is: Can i do this without using cursor?
Please give me a solution.
Thanks in advance
February 24, 2010 at 10:29 pm
select
H
, max(case when D %3 = 1 then D else 0 end) D1
, max(case when D %3 = 2 then D else 0 end) D2
, max(case when D %3 = 0 then D else 0 end) D3
from
#A
group by
H, (D-1)/3
order by
H
for MS 2005
;with t as(
select
H, D, (D-1)/3 [grouping], D%3 [clmn]
from
#A
)
select
H, [1], [2], [0]
from
t
pivot (max(D) for [clmn] in ([1], [2], [0])) as pvt
I Have Nine Lives You Have One Only
THINK!
February 25, 2010 at 1:56 am
I have same problem but my data is:
create table A
(
H int,
D int
)
insert into A
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,3 union all
select 3,5 union all
select 3,10 union all
select 3,15 union all
select 3,16 union all
select 3,17
I have 1 variable @i
If @i = 3 then result is:
H D1 D2 D3
1 1 2 null
2 1 null null
3 1 3 5
3 10 15 16
3 17 null null
If @i = 5 then result is:
H D1 D2 D3 D4 D5
1 1 2 null null null
2 1 null null null null
3 1 3 5 10 15
3 16 17 null null null
Please give me a solution with @i = n 😀
February 25, 2010 at 5:14 am
;with t as(
select
H
, D
, (row_number() over (partition by H order by D) -1)/3 [grouping]
, (row_number() over (partition by H order by D) % 3) [clmn]
from
#A
)
select
H, [1], [2], [0]
from
t
pivot (max(D) for [clmn] in ([1], [2], [0])) as pvt
result is
112NULL
21NULLNULL
3135
3101516
317NULLNULL
it's right or not?
I Have Nine Lives You Have One Only
THINK!
February 25, 2010 at 5:27 am
Here you go...
selecth, ceiling(RN/3.0),
max(case when RN % 3 = 1 then D else NULL end) D1,
max(case when RN % 3 = 2 then D else NULL end) D2,
max(case when RN % 3 = 0 then D else NULL end) D3
from(
selectrow_number() over( partition by H order by D ) as RN, H, D
from#A
) t
GROUP BY H, ceiling(RN/3.0)
selecth, ceiling(RN/5.0),
max(case when RN % 5 = 1 then D else NULL end) D1,
max(case when RN % 5 = 2 then D else NULL end) D2,
max(case when RN % 5 = 3 then D else NULL end) D3,
max(case when RN % 5 = 4 then D else NULL end) D4,
max(case when RN % 5 = 0 then D else NULL end) D5
from(
selectrow_number() over( partition by H order by D ) as RN, H, D
from#A
) t
GROUP BY H, ceiling(RN/5.0)
--Ramesh
February 25, 2010 at 11:35 am
nguyennd (2/25/2010)
I have same problem but my data is:
create table A(
H int,
D int
)
insert into A
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,1 union all
select 3,3 union all
select 3,5 union all
select 3,10 union all
select 3,15 union all
select 3,16 union all
select 3,17
I have 1 variable @i
If @i = 3 then result is:
H D1 D2 D31 1 2 null
2 1 null null
3 1 3 5
3 10 15 16
3 17 null null
If @i = 5 then result is:
H D1 D2 D3 D4 D51 1 2 null null null
2 1 null null null null
3 1 3 5 10 15
3 16 17 null null null
Please give me a solution with @i = n 😀
See the following... it will require some dynamic SQL...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply