|
Home > Archive > MS SQL Server > November 2006 > convert rows to columns
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
convert rows to columns
|
|
| Ashutosh 2006-11-15, 7:14 pm |
| 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
| |
| Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA 2006-11-15, 7:14 pm |
| 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
>
>
| |
| Orion22 2006-11-16, 12:16 am |
|
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
| |
|
|
|
|
|