SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Complex UNPIVOT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SCHEMA
Posting Yak Master

192 Posts

Posted - 04/16/2013 :  23:50:44  Show Profile  Reply with Quote
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

Edited by - SCHEMA on 04/16/2013 23:54:16

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/17/2013 :  00:46:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000