Sql Server Pivot Rows into Columns With Same Value for rest of the columns

Question

I have table in SQL Server called test having 3 column

Please refer this image.Click to view

Using this query :

IF OBJECT_ID('tempdb..#tblcolumn') IS NOT NULL DROP TABLE #tblcolumn
create table #tblcolumn(PeriodName varchar(30), PeriodID int,accountid int)
insert into #tblcolumn values('p1',1,110)
insert into #tblcolumn values('p2',2,111)
insert into #tblcolumn values('p3',3,112)
insert into #tblcolumn values('p4',4,113)
insert into #tblcolumn values('p5',5,114)
insert into #tblcolumn values('p6',6,115)
insert into #tblcolumn values('p7',7,116)

select * from #tblcolumn

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
    DECLARE @colsWithNoNulls    NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(PeriodName) 
                    from #tblcolumn
                    group by PeriodName
                    order by PeriodName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols

set @query = 'SELECT  accountid,'+@cols+' into #temp1 from  #tblcolumn             
            pivot 
            (
                sum(PeriodID)
                for PeriodName in (' + @cols + ')
            ) p '
            set @query = @query+ 'select * from #temp1'
execute(@query);

Any help would be appreciated. Thanks.

Answer 1

You can use sum() over() from your output to get that results as below:

DECLARE @cols AS NVARCHAR(MAX),
        @Colsover AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
    DECLARE @colsWithNoNulls    NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(PeriodName) 
                    from #tblcolumn
                    group by PeriodName
                    order by PeriodName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @Colsover = STUFF((SELECT ','+QUOTENAME(PeriodName) +' = sum('+ QUOTENAME(PeriodName) +') over()'
                    from #tblcolumn
                    group by PeriodName
                    order by PeriodName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols
select @Colsover

set @query = 'SELECT accountid, '+ @Colsover+' from ( SELECT  *  from  #tblcolumn             
            pivot 
            (
                sum(PeriodID)
                for PeriodName in (' + @cols + ')
            ) p ) a ;'
           -- set @query = @query+ 'select * from #temp1'
execute(@query);

output:

+-----------+----+----+----+----+----+----+----+
| accountid | p1 | p2 | p3 | p4 | p5 | p6 | p7 |
+-----------+----+----+----+----+----+----+----+
|       110 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       111 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       112 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       113 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       114 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       115 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
|       116 |  1 |  2 |  3 |  4 |  5 |  6 |  7 |
+-----------+----+----+----+----+----+----+----+