1

I'm a little bit stuck and i'm hoping someone can point me in the right direction. I'm attempting to flatten out a table into multiple columns from x rows. This is only an example and the real version contain many more rows that could change at anytime.

/*
HELLO  |  WORLD   |  FOO   |  BAR
 0          0         0        0
*/

CREATE TABLE #HI
(
ID INT IDENTITY(1,1),
COLNAME NVARCHAR(255) DEFAULT('MISSING'),
VAL INT DEFAULT(0)
);

INSERT INTO #HI(COLNAME)VALUES (N'HELLO'),(N'WORLD'),(N'FOO'),(N'BAR') --this can change over the course of a day so hardcoding isn't an option has to be dynamic names.

SELECT  *
FROM 
(
SELECT val, COLNAME
FROM #HI) sourcetbl
PIVOT (MAX(COLNAME) FOR COLNAME IN (
[a]
)) AS pvt
--i think i could use dynamic sql .. haven't looked into this option much yet.  I'm hoping pivot will work
SELECT DISTINCT  COLNAME   FROM 
  #HI A 
  CROSS APPLY 
  ( SELECT COLNAME + ',' FROM #HI B 
  WHERE A.id = B.id ORDER By COLNAME FOR XML PATH('') ) AS C (DerivedColumn)

DROP TABLE #HI;
StuartLC
  • 104,537
  • 17
  • 209
  • 285
xascendent
  • 11
  • 1
  • Please tag SqlServer if this is MS-SQL. If the column names aren't fixed, you'll need to resort to dynamic SQL. [BlueFeet](https://stackoverflow.com/a/10404455/314291) has several examples of this. – StuartLC Nov 22 '17 at 06:07
  • 1
    That example was perfect. ty. – xascendent Nov 22 '17 at 06:28

0 Answers0