soozh

asked on 7/23/2015

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?

Microsoft SQL Server 2008SQL

Last Comment

ashishsingh4u

8/22/2022 - Mon

Mike Eghtebas

7/23/2015

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

7/23/2015

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

7/23/2015

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Éric Moreau

7/23/2015

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

7/23/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

PortletPaul

7/24/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ashishsingh4u

10/21/2015

Consider using SQL pivot for this.