InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:25:34 GMT20http://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxSomething changed on my VM, but not sure what because I didn't change anything. All of a sudden this is the normal results:UNION method: Elapsed Time (ms) 2665UNPIVOT method: Elapsed Time (ms) 1257Tue, 11 Sep 2012 11:58:59 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxSet up a quick test here as well:UNION method: Elapsed Time (ms) 1339UNPIVOT method: Elapsed Time (ms) 1181Ran it several times with similar results. Sorry, didn't set up a test for Dwain Camps method.Tue, 11 Sep 2012 11:47:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxI'm not sure about performance from Lynn's solution against UNPIVOT.But there's another solution in here that has been proved to be better than UNPIVOT by Dwain in this article.[url]http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/[/url]Could that be enough? Or someone is willing to make a complete test?Mon, 10 Sep 2012 20:54:02 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr]So does one also need to run tests to prove that:SELECT a, b, c, d, e FROM dbo.table1is more efficient than:SELECT a, null, null, null, null FROM dbo.table1 UNION ALLSELECT null, b, null, null, null FROM dbo.table1 UNION ALLSELECT null, null, c, null, null FROM dbo.table1 UNION ALLSELECT null, null, null, d, null FROM dbo.table1 UNION ALLSELECT null, null, null, null, e FROM dbo.table1??Because that's a roughly equivalent comparison.[/quote]Maybe. All I'm saying is that I don't know anything about PIVOT/UNPIVOT. Although I have not tested it, it might only be doing one table scan, but what else is it doing? It could be you are absolutely correct, but were I the OP, I would certainly want to test it for myself.Mon, 10 Sep 2012 20:21:10 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Lynn Pettis (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Lynn Pettis (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?[/quote]I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.[/quote]I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.[/quote]And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?[/quote]So does one also need to run tests to prove that:SELECT a, b, c, d, e FROM dbo.table1is more efficient than:SELECT a, null, null, null, null FROM dbo.table1 UNION ALLSELECT null, b, null, null, null FROM dbo.table1 UNION ALLSELECT null, null, c, null, null FROM dbo.table1 UNION ALLSELECT null, null, null, d, null FROM dbo.table1 UNION ALLSELECT null, null, null, null, e FROM dbo.table1??Because that's a roughly equivalent comparison.Mon, 10 Sep 2012 17:09:52 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Lynn Pettis (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?[/quote]I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.[/quote]I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.[/quote]And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?Mon, 10 Sep 2012 17:04:16 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?[/quote]Probably the main reason, albeit not a valid one, is, similar to what Lynn said, I've never used PIVOT/UNPIVOT, I just happened to help someone with PIVOT on another forum a couple of days ago, and this question seemed like a good opportunity to try UNPIVOT. Other than that I don't know anything about it from a performance perspective. Sounds like a good opportunity for a million row test. Are there any indexes on the table in question?Mon, 10 Sep 2012 16:50:30 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Lynn Pettis (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?[/quote]I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.[/quote]I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.Mon, 10 Sep 2012 16:28:12 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr][quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?[/quote]I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.Mon, 10 Sep 2012 16:24:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Greg Snidow (9/10/2012)[/b][hr][quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.[/quote]Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?Mon, 10 Sep 2012 16:18:41 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.Mon, 10 Sep 2012 16:14:06 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxWhy not start with something like this to build your query or inserts around...?[code="sql"]SELECT name,addy,ph,subject,gradeFROM #test CROSS APPLY (VALUES('subject 1',grade1),('subject 2',grade2),('subject 3',grade3),('subject 4',grade4),('subject 5',grade5)) AS x(subject,grade)[/code]Mon, 10 Sep 2012 12:30:47 GMTSQL Padawanhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Phil Parkin (9/10/2012)[/b][hr][quote][b]Lynn Pettis (9/7/2012)[/b][hr][quote][b]Phil Parkin (9/7/2012)[/b][hr]Lynn's solution is the way to go. In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.[/quote]Should read:[quote]In [strike]her[/strike] his solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.[/quote]Not a biggie, happens rather frequently.[/quote]Eek, I apologise for this!:blush:[/quote]Like I said, not a problem. It happens regularly with me, in fact it happened this weekend before a soccer game I was officiating. One of the other refs though we were waiting for a lady to join us while the third official was working the game before ours.Mon, 10 Sep 2012 09:57:54 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]ScottPletcher (9/10/2012)[/b][hr]The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.[/quote]Probably, but I am still new at writing pivot/unpivot queries so I will leave that one to others.Mon, 10 Sep 2012 09:55:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Lynn Pettis (9/7/2012)[/b][hr][quote][b]Phil Parkin (9/7/2012)[/b][hr]Lynn's solution is the way to go. In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.[/quote]Should read:[quote]In [strike]her[/strike] his solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.[/quote]Not a biggie, happens rather frequently.[/quote]Eek, I apologise for this!:blush:Mon, 10 Sep 2012 09:39:57 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxThe UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Mon, 10 Sep 2012 09:38:01 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxNo, you put it outside the WITH statement.Here's the reference to the syntax:[url]http://msdn.microsoft.com/en-us/library/ms174335(v=sql.105).aspx[/url]And here's is the example:[url]http://msdn.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#OtherTables[/url]Fri, 07 Sep 2012 10:33:36 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxNope, more like this:[code="sql"]with basedata ( name, addy, ph, grade) as (select name, addy, ph, grade1from sourcetableunion allselect name, addy, ph, grade2from sourcetableunion allselect name, addy, ph, grade3from sourcetableunion allselect name, addy, ph, grade4from sourcetableunion allselect name, addy, ph, grade5from sourcetable)INSERT INTO dest_table( name, addy, ph, grade)select name, addy, ph, gradefrom BaseDatawhere grade is not null;[/code]Fri, 07 Sep 2012 10:31:06 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxThanks guys.... i worked it out... now, if i were to turn the select into an insert statement?with basedata ( name, addy, ph, grade) as (select <--PUT INSERTS HERE? name, addy, ph, grade1from sourcetableunion allselect name, addy, ph, grade2from sourcetableFri, 07 Sep 2012 10:14:35 GMTrobert.baird 2778http://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]robert.baird 2778 (9/7/2012)[/b][hr]ok.... i got it... the object referenced at the end is the basedata. so looking at the result set, i see all of the values.... but that leaves me with another issue... i see the 'grades' but not the subjects... this is frustrating...lol[/quote]Tell you what, it wouldn't be frustrating if you provided all the information up front instead of providing things piecemeal.The best place to start, please read the first article I reference below in my signature block about asking for help. It gives a step by step approach on what and how to post the information needed to provide you with the best possible answers. I haven't read it in a while myself, so I think it may leave out that we also need to know the expected results based on the sample data you need to post.Fri, 07 Sep 2012 09:54:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspx[quote][b]Phil Parkin (9/7/2012)[/b][hr]Lynn's solution is the way to go. In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.[/quote]Should read:[quote]In [strike]her[/strike] his solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.[/quote]Not a biggie, happens rather frequently.Fri, 07 Sep 2012 09:48:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxYou never mentioned the subjects.You have to add them to the query that constructs the basedata (the selects with union all).[code="sql"]with basedata ( name, addy, ph, subject, grade) as (select name, addy, ph, 'Subject 1', grade1from sourcetableunion allselect name, addy, ph, 'Subject 2', grade2from sourcetableunion all...[/code]Fri, 07 Sep 2012 09:41:14 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxok.... i got it... the object referenced at the end is the basedata. so looking at the result set, i see all of the values.... but that leaves me with another issue... i see the 'grades' but not the subjects... this is frustrating...lolFri, 07 Sep 2012 09:04:21 GMTrobert.baird 2778http://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxOkay... i read through them, and have a point or two of confusion. In my example above, my source has 'grade1', 'grade2', etc.... however it seems that she is referencing a column that doesn't exist - 'grade'... and after i convert this to my scenerio, it states that the 'grade column doesn't exist and errors out. Also, the very last portion (select outside the parents) seems to be missing the FROM object reference...Do I need to add the 'grade' column somewhere, in the queried table perhaps?DanFri, 07 Sep 2012 08:48:32 GMTrobert.baird 2778http://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxTo understand the WITH basedata, you should read this:[url]http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx[/url]and this:[url]http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx[/url]Fri, 07 Sep 2012 08:28:56 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxLynn's solution is the way to go. In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.Fri, 07 Sep 2012 08:27:22 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxThanks! I understand the unions and can see how it puts it together in a serial fashion, not sure on the 'with basedata' portion though.... could you elaborate on that just a bit?ThanksDanFri, 07 Sep 2012 08:09:04 GMTrobert.baird 2778http://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxDan, I think Lynn's solution is probably better, but just for academic purposes, you could try something like this...[code]IF OBJECT_ID('tempdb..#test','u') IS NOT NULL DROP TABLE #test;SELECT 'Dan' AS [name], '123 Main' AS addy, '555-5555' AS ph, CAST('A' AS VARCHAR(20)) AS grade1, CAST(NULL AS VARCHAR(20)) AS grade2, CAST('C+' AS VARCHAR(20)) AS grade3, CAST(NULL AS VARCHAR(20)) AS grade4, CAST(NULL AS VARCHAR(20)) AS grade5 INTO #test;SELECT [name],addy,ph,grade FROM ( SELECT [name], addy, ph, grade1, grade2, grade3, grade4, grade5 FROM #test) t UNPIVOT ( grade FOR x IN ([grade1],[grade2],[grade3],[grade4],[grade5]) ) AS unpvtWHERE grade IS NOT NULL[/code]Tue, 04 Sep 2012 15:30:36 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxShould do something like this:[code="sql"]with basedata ( name, addy, ph, grade) as (select name, addy, ph, grade1from sourcetableunion allselect name, addy, ph, grade2from sourcetableunion allselect name, addy, ph, grade3from sourcetableunion allselect name, addy, ph, grade4from sourcetableunion allselect name, addy, ph, grade5from sourcetable)select name, addy, ph, gradefrom BaseDatawhere grade is not null;[/code]Tue, 04 Sep 2012 13:12:49 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354133-392-1.aspxHi all - I have an interesting scenario that i would like some input on. I have a requirement to import data from one system into another. The source table is not normalized to the destination.The first (source) table will have a few pieces of common info, and the last several columns will have data in various columns. As an example, each row looks as follows:[b]name, addy, ph, grade1, grade2, grade3, grade4, grade5[/b]Dan, 123 Main, 555-5555, A, NULL, C+, NULL, NULL...Now, in the destination table, the import needs to omit the records with any NULL grades. As an example (based on the above):[b]name, addy, ph, grade[/b]Dan, 123 Main, 555-5555, ADan, 123 Main, 555-5555, C+...My question I guess is, how is the best way to do this? Ultimately, I will want this in an integration serv package. My first thought was to pre-process the records on import with a CASE / IMPORT type solution, but not sure if that will work using case...Any suggestions would be appreciated.Thanks,DanTue, 04 Sep 2012 13:05:58 GMTrobert.baird 2778