Remove From My Forums

Pivot in SQL

Question

User-582711651 posted

Hello Friends,

Pls help me, how to do this using Pivot.

This is my Temp table.

CREATE Table #TmpTbl_Resolvers 
(TicketType VARCHAR(10),ResolverID VARCHAR(12),ResolverECode VARCHAR(12) NULL,ResolverName VARCHAR(200) NULL,ResolverRole VARCHAR(7) NULL,TicketsCount INT, UpdtTimeStamp Datetime)

INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ABB','5001','E05001','Perumal','MGR','2','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ACC','5001','E05001','Perumal','MGR','6','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('IND','5002','E05002','Jai Sai','EDP','7','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ACC','5002','E05002','Jai Sai','EDP','89','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
Values ('RSV','5002','E05002','Jai Sai','EDP','72','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
Values ('TNT','5007','E05007','Shivam','MGR','33','2021/05/01')

Select * from #TmpTbl_Resolvers

Expected Result:

ResolverID	ResolverECode	ResolverName	ResolverRole	ABB	ACC	IND	RSV	TNT	Total	UpdtTimeStamp
5001	E05001	Perumal	MGR	2	6				8	01/05/2021:00
5002	E05002	Jai Sai	EDP		89	7	72		168	01/05/2021:00
5007	E05007	Shivam	MGR					33	33	01/05/2021:00
			Total	2	95	7	72	33	209

Thanks in advance.

Tuesday, May 25, 2021 6:45 AM

Answers

User1535942433 posted

Hi ayyappan.CNN,

According to your requirement,you could try this:

CREATE Table #TmpTbl_Resolvers 
(TicketType VARCHAR(10),ResolverID VARCHAR(12),ResolverECode VARCHAR(12) NULL,ResolverName VARCHAR(200) NULL,ResolverRole VARCHAR(7) NULL,TicketsCount INT, UpdtTimeStamp Datetime)

INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ABB','5001','E05001','Perumal','MGR','2','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ACC','5001','E05001','Perumal','MGR','6','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('IND','5002','E05002','Jai Sai','EDP','7','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
 Values ('ACC','5002','E05002','Jai Sai','EDP','89','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
Values ('RSV','5002','E05002','Jai Sai','EDP','72','2021/05/01')
INSERT INTO #TmpTbl_Resolvers (TicketType,ResolverID,ResolverECode,ResolverName,ResolverRole,TicketsCount,UpdtTimeStamp)
Values ('TNT','5007','E05007','Shivam','MGR','33','2021/05/01')

Select * from #TmpTbl_Resolvers


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
	@colsForSum AS NVARCHAR(MAX);
SET @cols = STUFF(ISNULL(@cols + ',','')+(SELECT DISTINCT ',' + QUOTENAME(c.TicketType) 
            FROM #TmpTbl_Resolvers c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
SELECT @colsForSum = REPLACE(@cols,',','+')
SELECT @colsForSum = REPLACE(@colsForSum,'[','ISNULL([')
SELECT @colsForSum = REPLACE(@colsForSum,']','],0)')

DECLARE @RowForSum AS NVARCHAR(MAX)
SELECT @RowForSum = REPLACE(@cols,',',',')
SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
SET @query = 
';with cte
 AS(
    SELECT * from 
	(
        SELECT ResolverID,ResolverECode,ResolverName,ResolverRole, ' + @cols + ', SUM(' + @colsForSum + ') Total,UpdtTimeStamp from 
        (
                select TicketType
                    ,ResolverID
                    ,ResolverECode
		    ,ResolverName
	            ,ResolverRole
		    ,UpdtTimeStamp
		    ,TicketsCount
                from #TmpTbl_Resolvers 
         )x
         pivot 
         (
              max(TicketsCount)
              for TicketType in (' + @cols + ')
         ) p group by ResolverID,ResolverECode,ResolverName,ResolverRole, UpdtTimeStamp, '+ @cols +'
	 )r
  )
  SELECT ResolverID,ResolverECode,ResolverName,ResolverRole, ' + @cols + ', SUM(' + @colsForSum + ') Total,UpdtTimeStamp from cte
  group by ResolverID,ResolverECode,ResolverName,ResolverRole, UpdtTimeStamp,' + @cols + '
  UNION ALL
  SELECT '''','''','''',''ToTal'','+ @RowForSum +',SUM(Total),NULL from cte'

EXECUTE(@query)

Result:

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Wednesday, May 26, 2021 5:45 AM

Answered by:

Pivot in SQL

Question

Answers