SQL PIVOT one column only

pivot, sql, sql-server

Solution

A more general approach is to add a row number and use it as anchor

WITH _ID AS (
  SELECT Valoare
       , _ID = Row_Number() OVER (ORDER BY Valoare)
  FROM   GEConfig
)
SELECT [1] = MAX(CASE WHEN _ID = 1 THEN Valoare ELSE NULL END)
     , [2] = MAX(CASE WHEN _ID = 2 THEN Valoare ELSE NULL END)
     , [3] = MAX(CASE WHEN _ID = 3 THEN Valoare ELSE NULL END)
     , [4] = MAX(CASE WHEN _ID = 4 THEN Valoare ELSE NULL END)
     , [5] = MAX(CASE WHEN _ID = 5 THEN Valoare ELSE NULL END)
     , [6] = MAX(CASE WHEN _ID = 6 THEN Valoare ELSE NULL END)
     , [7] = MAX(CASE WHEN _ID = 7 THEN Valoare ELSE NULL END)
     , [8] = MAX(CASE WHEN _ID = 8 THEN Valoare ELSE NULL END)
FROM   _ID

Static case

You can use a real `PIVOT` as in the answer from MarkD, or you can use a fake one

SELECT [1] = MAX(CASE WHEN Valoare = 'aaa' THEN 'aaa' ELSE NULL END)
     , [2] = MAX(CASE WHEN Valoare = 'bbb' THEN 'bbb' ELSE NULL END)
     , [3] = MAX(CASE WHEN Valoare = 'ccc' THEN 'ccc' ELSE NULL END)
     , [4] = MAX(CASE WHEN Valoare = 'ccc' THEN 'ccc' ELSE NULL END)
     , [5] = MAX(CASE WHEN Valoare = 'ddd' THEN 'ddd' ELSE NULL END)
     , [6] = MAX(CASE WHEN Valoare = 'eee' THEN 'eee' ELSE NULL END)
     , [7] = MAX(CASE WHEN Valoare = 'fff' THEN 'fff' ELSE NULL END)
     , [8] = MAX(CASE WHEN Valoare = 'ggg' THEN 'ggg' ELSE NULL END)
FROM   GEConfig

Problem

So I have this "table" which is a result of the query ``` SELECT Valoare FROM GEConfig WHERE Cimp IN('Societate','Adresa','Banca','CapitalSocial','Cont','CUI','NrRegCom','ModulReceptiiExtCotaTVA') ``` GeConfig is a table what is used to configurate the aplication for each client so the data that will result from the query above will be diferent for eaech client. ``` Valoare ======================== 1 aaa 2 bbb 3 ccc 4 ddd 5 eee 6 fff 7 ggg 8 hhh ``` I want to pivot this table so it will look like ``` col1 col2 col3 col4 col5 col6 col7 col8 aaa bbb ccc ddd eee fff ggg hhh ``` I don't have any aggregate, I only have that one Column with 8 rows which I want to make into 1 row with 8 columns. Why I want this? I have to use into Rave Report. I tried something like this ``` select Valoare , [1] , [2] , [3] from ( select Valoare from GEConfig ) x pivot ( max(Valoare) for Valoare in([1], [2], [3]) )p ``` But it clear that is very very wrong as I have no idea on how to use a PIVOT.

Original source