April 3, 2014 at 5:01 am
Hi all,
I want a help for a dynamic pivoting for columns, I am struck with it. I want this dynamic pivoting query to used in the jasperreport so it has to be in select statement rather than dynamic query.
For a note, dynamic pivot without dynamic sql query.
Had written query but throwing error so if anyone could help me to proceed on this will be frutiful
DECLARE @tagDetails TABLE ( tagsetId VARCHAR(255), tagName VARCHAR(255), description VARCHAR(500),
mandatory VARCHAR(10),multiSelect VARCHAR(10),objecttype VARCHAR(255),
typeName VARCHAR(255))
INSERT INTO @tagDetails
SELECTvt.tagset_id AS tagsetId,
vt.tagset_name AS tagName,
vt.description AS description,
vt.is_mandatory AS mandatory,
vt.is_multi_select AS multiSelect,
mot.object_type AS objecttype,
(SELECT Name FROM Object_Type_Details WHERE object_type = mot.object_type) AS typeName
FROMView_TagSet vt
INNER JOIN Map_Object_Type mot ON mot.map_id = vt.tagset_id WHERE vt.type = 'Tag'
ORDER BY vt.tagset_name
--SELECT * FROM @tagDetails
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(typeName as varchar) + ']','[' + cast(typeName as varchar)+ ']')
FROM (SELECT DISTINCT typeName FROM @tagDetails) s
DECLARE @PivotColumn VARCHAR(MAX)
SET @PivotColumn = @PivotColumnHeaders
SELECT *
FROM
(
SELECT tagsetId, tagName, description, mandatory, multiSelect
FROM @tagDetails
) AS SOURCE
PIVOT
(
MAX(objecttype)
FOR typeName IN ( @PivotColumn )
)
) AS PivotTable
April 3, 2014 at 5:54 am
See the syntax of dynamic sql .
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ConditionId)
FROM tablename c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT NId, ' + @cols + ' from
(
select NId, ConditionId, ConditionResult
from tablename
) x
pivot
(
sum(ConditionResult)
for ConditionId in (' + @cols + ')
) p '
April 3, 2014 at 9:41 am
You might want to check this article:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply