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.
Query to convert colums to row
Hello,
I have a table that looks like:
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.
All the nulls should be excluded.
Is this some type of unpivot?
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,
........
)
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,
All the nulls should be excluded.
Is this some type of unpivot?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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:
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
...
hej!
I can see i have not explained the data correctly. When i wrote:
i was giving the values of the columns in one row meaning:
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
I can see i have not explained the data correctly. When i wrote:
1,2,3,4,5,,,,,,
6,7,8,,,,,,,,
9,,,,,,,,,,
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, , , , , , , , , ,
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
in this case why not just:
select Atgard1 from SKRS_OriginalData
union all
select Atgard2 from SKRS_OriginalData
union all
...
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
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);
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.
For more on this technique see: Spotlight on UNPIVOT, Part 1
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
;
For more on this technique see: Spotlight on UNPIVOT, Part 1
Premium Content
You need an Expert Office subscription to comment.Start Free Trial