convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
A
Try this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:

> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>
|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
|||Check out RAC.
www.rac4sql.net

convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
ATry this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A|||Check out RAC.
www.rac4sql.net

convert rows to columns

I hope I am able to explain you my situation
I have a table called levelAllocation. A snapshot of the table looks as
follows
LAID LVID ALID PERCENT
1 1 4 10
2 1 5 20
3 1 6 30
4 2 4 45
5 2 5 55
6 2 6 65
I want a stored procedure that would output following
LAID LVID ALID4 ALID5 ALID6 PERCENT
1 1 4 NULL NULL 10
2 1 NULL 5 NULL 20
3 1 NULL NULL 6 30
4 2 4 NULL NULL 45
5 2 NULL 5 NULL 55
6 2 NULL NULL 6 65
Any other suggestion will be welcomed.
Regards,
ATry this:
(edit with your table name)
select t1.LAID
, t1.LVID
, t2.ALID4
, t2.ALID5
, t2.ALID6
, t1.[Percent]
from [table_name] t1
inner join (
select LAID
, (case ALID when 4 then ALID else NULL end) AS ALID4
, (case ALID when 5 then ALID else NULL end) AS ALID5
, (case ALID when 6 then ALID else NULL end) AS ALID6
from [table_name]
group by LAID, ALID
) t2
on t1.LAID = t2.laid
"Ashutosh" wrote:

> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A
>|||Yes , you can search for pivot tables, that is what i think you're
looking for.
Ashutosh wrote:
> I hope I am able to explain you my situation
> I have a table called levelAllocation. A snapshot of the table looks as
> follows
> LAID LVID ALID PERCENT
> 1 1 4 10
> 2 1 5 20
> 3 1 6 30
> 4 2 4 45
> 5 2 5 55
> 6 2 6 65
> I want a stored procedure that would output following
> LAID LVID ALID4 ALID5 ALID6 PERCENT
> 1 1 4 NULL NULL 10
> 2 1 NULL 5 NULL 20
> 3 1 NULL NULL 6 30
> 4 2 4 NULL NULL 45
> 5 2 NULL 5 NULL 55
> 6 2 NULL NULL 6 65
> Any other suggestion will be welcomed.
> Regards,
> A|||Check out RAC.
www.rac4sql.net