Pivoting Column to Row 1

[PCHomepage]

I see lots of posting here and elsewhere about pivoting but nothing that seems to work for me. I have a rather convoluted query that pulls up a short set of data as a column but I need it to be a row. If needed, Column1 contains "column" names. Any ideas or is there a stored procedure there to do it?

SELECT Column2 FROM `test_table` 
WHERE Column1 NOT IN (SELECT Column1 FROM `test_table`
WHERE (Column1 <= '@' OR Column1 >= '{'))
AND Column1 NOT IN (SELECT Column1 FROM `test_table`
WHERE Column1 LIKE '%a_param%')
AND Column1 != ''
AND Column1 != 'Keyword'
AND Column1 != 'Name'
AND Column1 NOT LIKE 'D%'

 

[r937]

what you're asking to do is not possible unless you know in advance all of the column1 names that you're going to get, and you take the time to hardcode each value into a CASE expression...

SELECT CASE WHEN column1 = 'foo' THEN column2 ELSE NULL END AS foo
     , CASE WHEN column1 = 'bar' THEN column2 ELSE NULL END AS bar
     , .....
  FROM ( original query for column2 ) x

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Column1, As I mentioned, has the the column names. The column names and the data are vertical but it's always going to be a single set of about ten values of column names and data.

 

[r937]

if it's always only those 10 values, then please try my query and show the results, i'll help you collapse them onto one row (my query only splits them into separate columns)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Thank you. I gave it a try but it tells me that:

Every derived table must have its own alias.

 

[PCHomepage]

Oh, I see! I thought the X at the end was a typo but instead it's the alias it's complaining about. Now to get the data into a single row . . .

 

[r937]

show me your query and i'll do it for you

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Here is the actual query, thanks!

SELECT CASE WHEN Column1 = 'A' THEN Column2 ELSE NULL END AS A
     , CASE WHEN Column1 = 'Ba' THEN Column2 ELSE NULL END AS Ba
     , CASE WHEN Column1 = 'Bb' THEN Column2 ELSE NULL END AS Bb
     , CASE WHEN Column1 = 'Bc' THEN Column2 ELSE NULL END AS Bc
     , CASE WHEN Column1 = 'D' THEN Column2 ELSE NULL END AS D
     , CASE WHEN Column1 = 'Da' THEN Column2 ELSE NULL END AS Da
     , CASE WHEN Column1 = 'Db' THEN Column2 ELSE NULL END AS Db
     , CASE WHEN Column1 = 'xHP' THEN Column2 ELSE NULL END AS xHP	  	  	  	  	       
  FROM (SELECT Column1, Column2 FROM `test_table` 
WHERE Column1 NOT IN (SELECT Column1 FROM `test_table`
WHERE (Column1 <= '@' OR Column1 >= '{'))
AND Column1 NOT IN (SELECT Column1 FROM `test_table`
WHERE Column1 LIKE '%param%')
AND Column1 != ''
AND Column1 != 'Bin'
AND Column1 != 'Device'
AND Column1 NOT LIKE 'DC%') X

 

[r937]

your original query looks a bit complicated

could you please confirm that this simpler version produces the same results --

SELECT Column1
     , Column2 
  FROM `test_table` 
 WHERE Column1 > '@' 
   AND Column1 < '{'
   AND NOT ( 
       Column1 LIKE '%param%'
    OR Column1 LIKE 'DC%' 
    OR Column1 IN ( '', 'Bin', 'Device' ) 
       )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Yes, it seems to give the proper result and it is much simpler! Thank you

 

[r937]

okay, here we go

your (simplified) query is nested as the innermost subquery

then the CASE expressions discombobulate the columns, but one per row, and that becomes a subquery nested within the main query, which collapses the multiple rows into one

SELECT MAX(A) AS A
     , MAX(Ba) AS Ba
     , MAX(Bb) AS Bb
     , MAX(Bc) AS Bc
     , MAX(D) AS D
     , MAX(Da) AS Da
     , MAX(Db) AS Db
     , MAX(xHP) AS xHP	  	  	  	  	       
  FROM ( SELECT CASE WHEN Column1 = 'A' THEN Column2 ELSE NULL END AS A
              , CASE WHEN Column1 = 'Ba' THEN Column2 ELSE NULL END AS Ba
              , CASE WHEN Column1 = 'Bb' THEN Column2 ELSE NULL END AS Bb
              , CASE WHEN Column1 = 'Bc' THEN Column2 ELSE NULL END AS Bc
              , CASE WHEN Column1 = 'D' THEN Column2 ELSE NULL END AS D
              , CASE WHEN Column1 = 'Da' THEN Column2 ELSE NULL END AS Da
              , CASE WHEN Column1 = 'Db' THEN Column2 ELSE NULL END AS Db
              , CASE WHEN Column1 = 'xHP' THEN Column2 ELSE NULL END AS xHP
           FROM ( SELECT Column1
                       , Column2 
                    FROM `test_table` 
                   WHERE Column1 > '@' 
                     AND Column1 < '{'
                     AND NOT ( 
                         Column1 LIKE '%param%'
                      OR Column1 LIKE 'DC%' 
                      OR Column1 IN ( '', 'Bin', 'Device' ) 
                         ) 
                ) AS s2               
       ) AS s1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

That's fantastic, thank you! I have another but it's much simpler (although more columns but a more basic query) so using this one as a template, I'll be able to easily get the other working too. Thanks again!

 

[PCHomepage]

Using this as an example, I created another query that has more lines, then wrapped both into INSERTs where the data is properly populated into the database.

These are both working perfectly but I've just come upon yet another set of "vertical data" this is large - about 108 quasi-columns from thousands of rows of similar data. The query I created accounts for all 108 values but apparently some sets do not contain everything so it's giving an error that one column name or another does not exist. It does exist but it is apparently not there in every new row so is there some way that it can be made ignore any that are not there while returning the rest?

These "rows" of data are each separated by a different FileID field value.

 

[r937]

The query I created accounts for all 108 values but apparently some sets do not contain everything so it's giving an error that one column name or another does not exist.

wouyld have to see the query and the error message to figure it out

if you could, you know, show some representative columns -- a few that work, one that doesn't -- not all 108 of them...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Sorry, it was my mistake as the MAX section had a field name that was missing from the CASE section. Not really too surprising with a query that's over 200 lines long! Fortunately the query is only for setup. Thank you for your help.