dynamic pivot in sql

Question

I have an following sql query using pivot function. I do not know what error i have in

this query. Please just help me out of this

DECLARE @DEPT VARCHAR(MAX)            
  SELECT @DEPT=COALESCE('@DEPT+','['+DEPTID+']','['+DEPTID+'])  
  FROM DEPARTMENTDEMO  
  DECLARE @QUERY VARCHAR(MAX)  
  SET @QUERY='SELECT '+@DEPT+'    
FROM (SELECT DEPTID  
FROM DEPARTMENTDEMO     
)AS SOURCETABLE  
PIVOT  
(DEPTNAME FOR DEPTID IN ('+@DEPT+')  
)AS PIVOTTABLE'

EXEC SP_EXECUTESQL @QUERY

`SELECT @DEPT=COALESCE('@DEPT+','+[DEPTID]+','+[DEPTID]')` is wrong enough, but there are other problems as well. Here's a dynamic pivot sample, work from it: http://stackoverflow.com/a/23159433/3464852 — dean, Apr 27 '14 at 05:32
i need the correct form.am getting an error like 'must declare the scalar variable dept' — javababy, Apr 27 '14 at 06:38
In the sample I linked to, there is a proper method how to obtain the list of columns, did you try it? — dean, Apr 27 '14 at 06:56
Actually my posted syntax is also the right one which we have got output in our class.But when am trying individually i just missed something such as single quote or that concatenation operator.i do not know where i went wrong — javababy, Apr 27 '14 at 06:59
If you insist using the unsupported syntax, this should probably work: `SELECT @DEPT=COALESCE(@DEPT+',['+DEPTID+']','['+DEPTID+']')`. You had quotes in wrong places. — dean, Apr 27 '14 at 07:04
yeah.you are exactly right.but till am not getting the **deptname**.instead if i use **count** function that is working fine. why am not getting the **deptname** column values? **@dean** — javababy, Apr 27 '14 at 07:18
No.it wont help.We cannot use max for **deptname**. It is showing error. — javababy, Apr 27 '14 at 09:39
possible duplicate of [Dynamic Pivot Columns in SQL Server](http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server) — Clockwork-Muse, Apr 27 '14 at 11:46

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

First of all, the expression used for creating the list of columns has some quotes in wrong places:

SELECT @DEPT=COALESCE(@DEPT+',['+DEPTID+']','['+DEPTID+']')

Also, take a look here for a proper way to create the column list (it's supported syntax and the order of elements is guaranteed).

For the actual PIVOT expression, you have to use some aggregation on the column. Try with MAX:

SET @QUERY= '
SELECT '+@DEPT+'    
FROM (
    SELECT DEPTID, DEPTNAME  
    FROM DEPARTMENTDEMO     
) AS SOURCETABLE  
PIVOT (
    MAX(DEPTNAME) FOR DEPTID IN ('+@DEPT+')  
) AS PIVOTTABLE'

edited May 23 '17 at 12:28

Community

1
1

answered Apr 27 '14 at 07:25

dean

9,960
2
25
26

No.it wont help.We cannot use max for **deptname**. It is showing error. – javababy Apr 27 '14 at 09:34
Because deptname wasn't included in sourcetable. See the edit – dean Apr 27 '14 at 09:51
Good. If I may ask, you said that "syntax is also the right one which we have got output in our class" -- which class would it be? Just curious. – dean Apr 27 '14 at 12:50
Training period in my company dude. **@dean** – javababy May 08 '14 at 16:21
OK, I'm not comenting then. Just accept the answer and let's all go on :) – dean May 08 '14 at 16:33

score 0 · Answer 2 · edited Jul 24 '14 at 13:17

I was just playing with dynamic pivoting the other day! Here is a fully functional testing script I created while doing it. Although it's based on a CROSS JOIN example, it should contain some useful insight. I hope you can glean some ideas about how to get your desired results.

IF OBJECT_ID(N'dbo.Strikes','U') IS NOT NULL
    DROP TABLE dbo.Strikes;

CREATE TABLE dbo.Strikes(
    Dimension nvarchar(10) NOT NULL PRIMARY KEY ,
    DimVal smallint DEFAULT 0);

INSERT INTO dbo.Strikes (Dimension, DimVal)
VALUES (N'Fire',5), (N'Water',4), (N'Earth',3), (N'Air',2), (N'Spirit',1), (N'George',0);


/* Dynamic Pivot Exmple */

--declare variables and cursor
DECLARE @PvtClm varchar(MAX)
DECLARE @PTmp AS varchar(10)
DECLARE PvtCsr CURSOR FOR
    SELECT DISTINCT Dimension 
    FROM dbo.Strikes 
    ORDER BY Dimension
--open and fill pivot column string
OPEN PvtCsr
    SET @PvtClm = N'[' 
    FETCH NEXT FROM PvtCsr INTO @PTmp
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @PvtClm = @PvtClm + RTRIM(@PTmp) + N'], ['
            FETCH NEXT FROM PvtCsr INTO @PTmp
        END
--kill cursor and finish pivot string
CLOSE PvtCsr
DEALLOCATE PvtCsr

--strip bogus end characters to finish pivot string
SET @PvtClm = LEFT(@PvtClm,(LEN(@PvtClm)-3));

--PRINT @PvtClm

--set the dynamic pivot sql string
DECLARE @DynPivot nvarchar(MAX)
SET @DynPivot = N'
WITH CrJoin AS (
    SELECT S1.Dimension AS SourceElement,
           S2.Dimension AS TargetElement,
           S1.DimVal - S2.DimVal AS Modifier
    FROM dbo.Strikes AS S1
        CROSS JOIN dbo.Strikes AS S2)

SELECT SourceElement, ' + @PvtClm + '
FROM CrJoin
PIVOT( 
    MAX(CrJoin.Modifier) 
    FOR CrJoin.TargetElement IN(' + @PvtClm + ')
    ) AS PvT;'

--PRINT @DynPivot

--run the pivot
EXEC sp_executesql @stmt = @DynPivot;


IF OBJECT_ID(N'dbo.Strikes','U') IS NOT NULL
    DROP TABLE dbo.Strikes;

dynamic pivot in sql

2 Answers2