June 14, 2012 at 8:45 am
HI All.
Think I'm just having one of them days today and cant get my head around this.
What I need to do is unpivot the following data into multiple rows.
DECLARE @SampleData TABLE (APUserID INT, FirstName NVARCHAR(256), Surname NVARCHAR(256), UserName NVARCHAR(256), Identifier NVARCHAR(256), School NVARCHAR(256), County NVARCHAR(4), NIN NVARCHAR(64), BirthPlace NVARCHAR(256))
INSERT INTO @SampleData VALUES (6241,'LAURA','FLORES OLVERA','M00001','M00001','JAIME TORRES BODET','','',''),
(6253,'JUAN','MARTÍNEZ ESCOBAR','M00013','M00013','GRACIANO SANCHEZ ROMO','','',''),
(6259,'JUAN','URESTI CHAVARRIA','M00019','M00019','JUSTO A. ZAMUDIO VARGAS','','','')
Expected outcome
APUserID, ProfileDescriptionItem, ProfileDescriptionValue
6241,1,'LAURA',
6241,2,'FLORES OLVERA',
6241,3,'M00001',
6241,4,'M00001',
6241,5,'JAIME TORRES BODET',
6241,6,'',
6241,7,'',
6241,8,'',
6253,1,'JUAN',
6253,2,'MARTÍNEZ ESCOBAR',
6253,3,'M00013',
6253,4,'M00013',
6253,5,'GRACIANO SANCHEZ ROMO'
6253,6,'',
6253,7,'',
6253,8,'',
6259,1,'JUAN',
6259,2,'URESTI CHAVARRIA',
6259,3,'M00019'
6259,4,'M00019',
6259,5,'JUSTO A. ZAMUDIO VARGAS'
6259,6,'',
6259,7,'',
6259,8,''
The column mappings relate to
Firstname column is ProfileDescriptiveItem # 1,
Surname column is ProfileDescriptiveItem # 2,
Username column is ProfileDescriptiveItem # 3,
Identifier column is ProfileDescriptiveItem # 4,
School column is ProfileDescriptiveItem # 5,
County column is ProfileDescriptiveItem # 6,
NIN column is ProfileDescriptiveItem # 7,
BirthPlace column is ProfileDescriptiveItem # 8
I have kindly voiced my opinion on having a vertical table instead of a horizontal table, but unfortunatly it cannot be changed due to project time limitation, but is on the cards for a future development cycle, hence the need to still do this unpivoting.
Thanks
June 14, 2012 at 9:22 am
anthony.green (6/14/2012)
HI All.Think I'm just having one of them days today and cant get my head around this.
What I need to do is unpivot the following data into multiple rows.
DECLARE @SampleData TABLE (APUserID INT, FirstName NVARCHAR(256), Surname NVARCHAR(256), UserName NVARCHAR(256), Identifier NVARCHAR(256), School NVARCHAR(256), County NVARCHAR(4), NIN NVARCHAR(64), BirthPlace NVARCHAR(256))
INSERT INTO @SampleData VALUES (6241,'LAURA','FLORES OLVERA','M00001','M00001','JAIME TORRES BODET','','',''),
(6253,'JUAN','MARTÍNEZ ESCOBAR','M00013','M00013','GRACIANO SANCHEZ ROMO','','',''),
(6259,'JUAN','URESTI CHAVARRIA','M00019','M00019','JUSTO A. ZAMUDIO VARGAS','','','')
Expected outcome
APUserID, ProfileDescriptionItem, ProfileDescriptionValue
6241,1,'LAURA',
6241,2,'FLORES OLVERA',
6241,3,'M00001',
6241,4,'M00001',
6241,5,'JAIME TORRES BODET',
6241,6,'',
6241,7,'',
6241,8,'',
6253,1,'JUAN',
6253,2,'MARTÍNEZ ESCOBAR',
6253,3,'M00013',
6253,4,'M00013',
6253,5,'GRACIANO SANCHEZ ROMO'
6253,6,'',
6253,7,'',
6253,8,'',
6259,1,'JUAN',
6259,2,'URESTI CHAVARRIA',
6259,3,'M00019'
6259,4,'M00019',
6259,5,'JUSTO A. ZAMUDIO VARGAS'
6259,6,'',
6259,7,'',
6259,8,''
The column mappings relate to
Firstname column is ProfileDescriptiveItem # 1,
Surname column is ProfileDescriptiveItem # 2,
Username column is ProfileDescriptiveItem # 3,
Identifier column is ProfileDescriptiveItem # 4,
School column is ProfileDescriptiveItem # 5,
County column is ProfileDescriptiveItem # 6,
NIN column is ProfileDescriptiveItem # 7,
BirthPlace column is ProfileDescriptiveItem # 8
I have kindly voiced my opinion on having a vertical table instead of a horizontal table, but unfortunatly it cannot be changed due to project time limitation, but is on the cards for a future development cycle, hence the need to still do this unpivoting.
Thanks
My guess is you can get it from here:
USE tempdb
GO
DECLARE @SampleData TABLE (APUserID INT, FirstName NVARCHAR(256), Surname NVARCHAR(256), UserName NVARCHAR(256), Identifier NVARCHAR(256), School NVARCHAR(256), County NVARCHAR(4), NIN NVARCHAR(64), BirthPlace NVARCHAR(256))
INSERT INTO @SampleData VALUES (6241,'LAURA','FLORES OLVERA','M00001','M00001','JAIME TORRES BODET','','',''),
(6253,'JUAN','MARTÍNEZ ESCOBAR','M00013','M00013','GRACIANO SANCHEZ ROMO','','',''),
(6259,'JUAN','URESTI CHAVARRIA','M00019','M00019','JUSTO A. ZAMUDIO VARGAS','','','')
SELECT * FROM @SampleData
SELECT APUserID, TypeId, Something
FROM
(SELECT APUserID, FirstName, Surname, UserName, Identifier, School, CAST(County AS NVARCHAR(256)) AS County, CAST(NIN AS NVARCHAR(256)) AS NIN, BirthPlace
FROM @SampleData) s
UNPIVOT
(Something FOR TypeId IN
(FirstName, Surname, UserName, Identifier, School, County, NIN, BirthPlace)
)AS unpvt;
GO
Jared
CE - Microsoft
June 14, 2012 at 9:26 am
Ok. Try this for the numbers:
SELECT APUserID, CAST(TypeId AS INT) AS TypeId, Something
FROM
(SELECT APUserID, FirstName AS '1', Surname AS '2', UserName AS '3', Identifier AS '4', School AS '5', CAST(County AS NVARCHAR(256)) AS '6', CAST(NIN AS NVARCHAR(256)) AS '7', BirthPlace AS '8'
FROM @SampleData) s
UNPIVOT
(Something FOR TypeId IN
([1], [2], [3], [4], [5], [6], [7], [8])
)AS unpvt;
GO
Jared
CE - Microsoft
June 14, 2012 at 10:28 am
Have you tried a VALUES list? With CROSS APPLY?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
June 14, 2012 at 8:49 pm
ChrisM@home (6/14/2012)
Have you tried a VALUES list? With CROSS APPLY?
Gosh Chris! Did you mean like this?
SELECT APUserID, TypeID, Something
FROM @SampleData
CROSS APPLY (
VALUES ('FirstName', FirstName)
,('Surname', SurName)
,('UserName', UserName)
,('Identifier', Identifier)
,('School', School)
,('County', County)
,('NIN', NIN)
,('BirthPlace', BirthPlace)) x(TypeID, Something)
I love this approach. I've run some timing tests vs. UNPIVOT and it seems to be quite efficient from a CPU standpoint.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 12:57 am
June 15, 2012 at 1:00 am
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.
I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.
Thanks again.
June 15, 2012 at 1:04 am
anthony.green (6/15/2012)
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.
Thanks again.
Here are a couple of links to get you started.
Spotlight on UNPIVOT by Brad Schulz
http://bradsruminations.blogspot.com/2010/02/spotlight-on-unpivot-part-1.html
Unpivoting data by Itzik Ben-Gan
http://www.sqlmag.com/article/database-administration/unpivoting-data
I just happened upon that approach by accident last week when I was playing around with VALUES sets.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 1:08 am
vinu512 (6/15/2012)
Hi Chris and Dwain.....this is really sweeet. 😀
You should see it vs. the double UNPIVOT. IMHO much cleaner. Runs faster too.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 1:13 am
just tried it on my local instance with the full sample data that we have been provided so far for the project
Values lists total execution time 51ms
Unpivot total execution time 58ms
Not a big difference, but we only have 127 rows of sample data to start the process off, when the project goes live in a couple of weeks we are talking around 100k horizontal rows into 800k vertical rows so I expect to see a major difference then.
June 15, 2012 at 1:16 am
My findings were overall (multiple runs) about 3% decrease in execution time on a single UNPIVOT. CPU savings were about 30% though.
Double UNPIVOT was 30% and 30%.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 2:56 am
dwain.c (6/14/2012)
ChrisM@home (6/14/2012)
Have you tried a VALUES list? With CROSS APPLY?Gosh Chris! Did you mean like this?
SELECT APUserID, TypeID, Something
FROM @SampleData
CROSS APPLY (
VALUES ('FirstName', FirstName)
,('Surname', SurName)
,('UserName', UserName)
,('Identifier', Identifier)
,('School', School)
,('County', County)
,('NIN', NIN)
,('BirthPlace', BirthPlace)) x(TypeID, Something)
I love this approach. I've run some timing tests vs. UNPIVOT and it seems to be quite efficient from a CPU standpoint.
That's the one! Thanks for the catch, Dwain - I posted on the way home.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 15, 2012 at 3:00 am
anthony.green (6/15/2012)
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.
Thanks again.
It's much easier to work with than UNPIVOT - the code is far easier to scan and understand.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 15, 2012 at 3:19 am
ChrisM@Work (6/15/2012)
anthony.green (6/15/2012)
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.
Thanks again.
It's much easier to work with than UNPIVOT - the code is far easier to scan and understand.
Is there a Dynamic Version to it as well??....Would love to have a look at that.
June 15, 2012 at 3:33 am
vinu512 (6/15/2012)
ChrisM@Work (6/15/2012)
anthony.green (6/15/2012)
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.I like the idea of the values list, never heard of that before, so will definatly give that a go and read up on that a bit more.
Thanks again.
It's much easier to work with than UNPIVOT - the code is far easier to scan and understand.
Is there a Dynamic Version to it as well??....Would love to have a look at that.
Vinu, I don't think there's any reason why you shouldn't write a dynamic sql statement which features a CROSS APPLY with a VALUES list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply