CODEWITHSUNDEEP
sqlsql-serverpivot
Boy Pasmo
Boy Pasmo

Reputation: 8491

How to use pivot query dynamically

I have here a query: (this was modified. thank you SO for the help) and I'm using mssql 2008

SELECT 'Risk','ADAB','Bahrain','Kuwait','Masirah','Qatar' <-- fixed
UNION ALL
select CONVERT(VARCHAR,risk)
,CONVERT(VARCHAR,[ADAB]) as ADAB <-- fixed
,CONVERT(VARCHAR,[Bahrain]) as Bahrain <-- fixed
,CONVERT(VARCHAR,[Kuwait]) as Kuwait <-- fixed
,CONVERT(VARCHAR,[Masirah]) as Masirah <-- fixed
,CONVERT(VARCHAR,[Qatar]) as Qatar <-- fixed

from (select risk, piv_site = risk, site
        from qcvqciffull
        where (1=1) AND Risk is not null) as ps
pivot (count(piv_site)
        for site in ([ADAB], [Bahrain], [Kuwait], [Masirah], [Qatar])) as pvt <-- fixed

I have here a fiddle. Is it possible to turn those fields who have "fixed" text beside it and turn them dynamically? Say, I have a new record coming in, USA for instance. Is there a way for it?

Any help would be much appreciated. Thanks.

Upvotes: 0

Views: 50

Answers (1)

Ajay2707
Ajay2707

Reputation: 5798

For current scenerio, you have to add whatever text will add other than this.

Or

you can go for dynamic pivot which automatically find and create column, where you have to create dynamic query and then execute.

Refer this :- Dynamic Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM #Tablename
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Upvotes: 1

Related Questions