Dynamically PIVOT on ONE AND ONLY ONE column from a single TABLE

user_17127245 0
Viewed 41

I've read multiple threads on this topic, and there does seem to be a way to do this dynamically, but I'm getting a syntax/compile error on the code.

I am trying to dynamically pull multiple rows for a temp table that simply has one column. The definition of the temp table is one field called acct_name. The challenge seems to be the "dynamics" of the fact that there will be over 50+ rows in the temp table, which is subject to change at any time.

I followed a previous poster's example, but am still getting compile/runtime errors.

I'm a rather novice SQL person, so you'll have to excuse the crudity of my code or my question.

DECLARE @idList varchar(500)
SELECT @idList = COALESCE(@idList + ',', '') + acct_name
FROM ##tmp_accts

DECLARE @sqlToRun varchar(1000)
SET @sqlToRun = '
SELECT * 
FROM ( 
    SELECT acct_name FROM ##tmp_accts
) AS src 
PIVOT ( 
    MAX(acct_name) FOR acct_name IN ('+ @idList +')  
) AS pvt'   

EXEC (@sqlToRun)

Does anyone have an obvious suggestion, I think it's very close to working.....

FOR EXAMPLE,

Let's say for sake of example we have the following acct_names - '12345','23456','34567','45678'.

The desire result is to return one row with 4 columns each with the respective value of acct_name. HOWEVER, the acct name is dynamic and is not known in advance, nor is the count of acct_name known in advance. A temp table is generated on the fly which determines all of the relevant acct_names for that particular run. It will vary with each run, each day that the query is run.

Thank you.....

dynamic-pivot pivot tsql sql-server
1 Answers

Thru an article available thru Microsoft, the following solution does the job apporpriately.....

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(acct_name) + ','
FROM 
    ##tmp_accts
GROUP BY 
    acct_name;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT DISTINCT acct_name 
    FROM 
        ##tmp_accts
) t 
PIVOT(
    COUNT(acct_name) 
    FOR acct_name IN ('+ @columns + ')
) AS piv;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
user_171272450
Related