Avatar of soozh
soozh
Flag for Sweden 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?
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
ashishsingh4u

8/22/2022 - Mon
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
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

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

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

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

Consider using SQL pivot for this.