Query to convert colums to row

soozh
soozh used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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 Consultant
Top Expert 2016

Commented:
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

soozhCEO

Author

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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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 Developer
Commented:
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

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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
ashishsingh4uAssociate

Commented:
Consider using SQL pivot for this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial