Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Complex UNPIVOT

Author  Topic 

SCHEMA
Posting Yak Master

192 Posts

Posted - 2013-04-16 : 23:50:44
How to do this

Sample Data

Act ActNumber [SR1_Male_Less than 20 years] [SR1_Male_Less than 30 years] [SR1_Male_Less than 40 years] [SR2_Enroll_Pat] [SR2_Enroll_Pat1] [SR2_Enroll_Pat2]
1 201 2 2 4 5 4 5

Expected OutPut

Act ActNumber Table Row Male Enroll
1 201 SR1 [Less than 20 years] 2 null
1 201 SR1 [Less than 30 years] 2 null
1 201 SR1 [Less than 40 years] 4 null
1 201 SR2 Pat null 5
1 201 SR2 Pat1 null 4
1 201 SR2 Pat2 null 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 00:46:37
here you go with the full illustration

--your sample table with the data
declare @t table
(
Act int,
ActNumber int,
[SR1_Male_Less than 20 years] int,
[SR1_Male_Less than 30 years] int,
[SR1_Male_Less than 40 years] int,
[SR2_Enroll_Pat] int,
[SR2_Enroll_Pat1] int,
[SR2_Enroll_Pat2] int
)
insert @t
values(1, 201, 2, 2, 4, 5, 4, 5)



--YOUR ACTUAL SOLUTION
select Act,ActNumber,
LEFT(cat,CHARINDEX('_',cat+'_')-1) AS [Table],
REVERSE(LEFT(REVERSE(cat),CHARINDEX('_',REVERSE(cat)+'_')-1)) AS [Row],
CASE WHEN cat LIKE '%Male%' THEN value END AS male,
CASE WHEN cat LIKE '%Enroll%' THEN value END AS Enroll
from @t
unpivot (value for cat in ([SR1_Male_Less than 20 years] ,
[SR1_Male_Less than 30 years] ,
[SR1_Male_Less than 40 years] ,
[SR2_Enroll_Pat] ,
[SR2_Enroll_Pat1] ,
[SR2_Enroll_Pat2]))u


output
---------------------------------------------------------------------------
Act ActNumber Table Row male Enroll
---------------------------------------------------------------------------
1 201 SR1 Less than 20 years 2 NULL
1 201 SR1 Less than 30 years 2 NULL
1 201 SR1 Less than 40 years 4 NULL
1 201 SR2 Pat NULL 5
1 201 SR2 Pat1 NULL 4
1 201 SR2 Pat2 NULL 5



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -