asked on

Query to convert colums to row

Hello,

I have a table that looks like:

CREATE TABLE [dbo].[SKRS_OriginalData](

    .........

	[Atgard1] [int] NULL,
	[Atgard2] [int] NULL,
	[Atgard3] [int] NULL,
	[Atgard4] [int] NULL,
	[Atgard5] [int] NULL,
	[Atgard6] [int] NULL,
	[Atgard7] [int] NULL,
	[Atgard8] [int] NULL,
	[Atgard9] [int] NULL,
	[Atgard10] [int] NULL,

    ........

)

Open in new window

I want to extract all the rows and create a result that lists all the values in columns Atgard1 to Atgard10 as a single column called Atgard.

So 

1,2,3,4,5,,,,,,
6,7,8,,,,,,,,
9,,,,,,,,,,

should become

1,
2,
3,
4,
5,
6,
7,
8,
9,

Open in new window

All the nulls should be excluded.

Is this some type of unpivot?

Mike Eghtebas

Select 
        Cast(IsNull([Atgard1],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard2],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard3],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard4],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard5],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard6],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard7],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard8],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard9],0) As Varchar(10))
        + ', ' + Cast(IsNull([Atgard10],0) As Varchar(10)) As Atgard
From Table1

Open in new window

Éric Moreau

you can use a function like the one from http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

and then call it with something like:

SELECT * FROM dbo.Split(select top 1 Atgard1 from SKRS_OriginalData, ',')
union all
SELECT * FROM dbo.Split(select top 1 Atgard2 from SKRS_OriginalData, ',')
union all
...

Open in new window

soozh

ASKER

hej!

I can see i have not explained the data correctly. When i wrote:

1,2,3,4,5,,,,,,
6,7,8,,,,,,,,
9,,,,,,,,,,

Open in new window

i was giving the values of the columns in one row meaning:

Atgard1, Atgard2, Atgard3, Atgard4, Atgard5, Atgard6, Atgard7, Atgard8, Atgard9
1,             2,             3,             4,             5,              ,             ,             ,                 ,                ,
6,             7,             8,               ,               ,              ,             ,             ,                 ,                ,
9,              ,                ,               ,               ,              ,             ,              ,                ,                 ,

Open in new window

and that i wanted the values to be converted into a single column called Atgard:

Atgard
1
2
3
4
5
6
7
8
9

Éric Moreau

in this case why not just:

select Atgard1 from SKRS_OriginalData
union all
select Atgard2 from SKRS_OriginalData
union all 
...

Open in new window

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ashishsingh4u

Consider using SQL pivot for this.