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?
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented: 
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 MoreauSenior .Net ConsultantCommented:
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

soozhCEOAuthor Commented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
Éric MoreauSenior .Net ConsultantCommented:
in this case why not just:
select Atgard1 from SKRS_OriginalData
union all
select Atgard2 from SKRS_OriginalData
union all 
...

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented: 
SELECT Atgard
from SKRS_OriginalData
unpivot(Atgard for id in(Atgard1, Atgard2, Atgard3, Atgard4, Atgard5, Atgard6, Atgard7, Atgard8, Atgard9)) As u
-- Order By Atgard  -- try with and without Order By

Open in new window

This solution has been tested using a temp table #SKRS_OriginalData I creatred. 
create table #SKRS_OriginalData(Atgard1 int, Atgard2 int, Atgard3 int, Atgard4 int, Atgard5 int, Atgard6 int, Atgard7 int, Atgard8 int, Atgard9 int);
Insert Into #SKRS_OriginalData(Atgard1, Atgard2, Atgard3, Atgard4, Atgard5, Atgard6, Atgard7, Atgard8, Atgard9) Values
(1, 2, 3, 4, 5, null, null, null, null)
,(6, 7, 8, null, null, null, null, null, null)
,(9, null, null, null, null, null, null, null, null);

Open in new window

PortletPaulEE Topic AdvisorCommented:
This will "unpivot" those columns into one. It may appear a little more complex than using UNPIVOT which you can see above. However if you have more columns to include into the result then the use of CROSS APPLY and VALUES is not only efficient but can be far easier to use.

select Atgard
from SKRS_OriginalData
cross apply (
  values 
         (Atgard1)
       , (Atgard2)
       , (Atgard3)
       , (Atgard4)
       , (Atgard5)
       , (Atgard6)
       , (Atgard7)
       , (Atgard8)
       , (Atgard9)
       , (Atgard10)
   ) CA (Atgard)
where atgard IS NOT NULL  
;

Open in new window


For more on this technique see: Spotlight on UNPIVOT, Part 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ashishsingh4uAssociateCommented:
Consider using SQL pivot for this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.