Dyanmic columns and PIVOT?

Tim Carmichael

I should remember this, but my mind is drawing a blank. In SQLServer 2008, I have a table: site, datapoint (there are other columns, but not important right now). I would like to produce a list of datapoints and the sites they are listed with. Example: Site DataPoint KC WindSpeed KC Temp CH WindSpeed CH Temp CH Power Output: Power CH Temp CH KC WindSpeed CH KC Can someone help me with the T-SQL? I can do this with temp tables and updates, but would prefer an elegant solution. Thanks, Tim

Richard Deeming

If I've understood your question properly, you're looking to concatenate row values rather than PIVOT them. This article[^] covers most of the options. For example, the black-box XML method:

SELECT
DataPoint,
STUFF
(
(
SELECT ',' + Site
FROM YourTable As I
WHERE I.DataPoint = O.DataPoint
ORDER BY Site
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')

    -- Remove the first comma:
    , 1, 1, ''
) As Sites

FROM
YourTable As O
GROUP BY
DataPoint
;

/*
Output:

DATAPOINT SITES

Power CH
Temp CH,KC
WindSpeed CH,KC
*/

http://sqlfiddle.com/#!3/5657e6/3/0[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Tim Carmichael

Thank you, but, no. Not concatenation. I need the CH and KC to be the column headers. What I have is 1400 data points and 4 sites; in theory, all sites SHOULD have the same datapoints, but... they don't. I need to create a matrix of datapoints and sites to show what is there and what isn't. What I've presented is the simplified version, but the concept is correct.

Richard Deeming

So if CH and KC are the column headers, what are the column values? Remember, you can't have different columns for different rows in the same resultset, which is what the "output" section of your question seems to be doing.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Tim Carmichael

Either the column name or the datapoint or 'Exists'; any indicator to show the datapoint exists for that site, but if it doesn't exist, a blank value.

Richard Deeming

So a basic dynamic pivot then?

DECLARE @cols As nvarchar(max), @query As nvarchar(max);

SET @cols = STUFF
(
(
SELECT DISTINCT ',' + QUOTENAME(Site)
FROM Source
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, ''
);

SET @query = N'SELECT DataPoint, ' + @cols + N' FROM Source PIVOT (COUNT(Site) FOR Site IN (' + @cols + N')) As p';

EXEC(@query);

/*
Output:

DATAPOINT CH KC
Power 1 0
Temp 1 1
WindSpeed 1 1
*/

http://sqlfiddle.com/#!3/5657e6/5/0[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Tim Carmichael

Thank you.. yes, that worked... like I said, I used to use it, but forgot how.