SQL Pivot

Need help with my query
CREATE Table #ActiveSessions
		(
			SiteName varchar(50),
			Region varchar(10),
			sDate datetime,
			avgUsage int
		)	

		DECLARE @DynamicPivotQuery as NVARCHAR(max), @ColumnName as NVARCHAR(MAX)	
		
		INSERT INTO #ActiveSessions
		select c.sitename,ddc.Region,FORMAT(c.SnapshotTime,'yyyy-MM-dd hh:00') as SnapshotTime, AVG(ActiveUsersCount) as AverageUserCount 
		FROM [Citrix].[dbo].[tblCitrixXDConnections] c WITH (NOLOCK)
		INNER JOIN [Citrix].[dbo].[tblCitrixDDC] ddc WITH (NOLOCK) ON c.siteName = ddc.DDCsite
		Where CAST(CAST(c.SnapshotTime as date) as varchar(10)) between @sdate and @edate and ddc.Region = @region
		GROUP BY c.sitename,ddc.Region,c.SnapshotTime 
		ORDER BY c.SnapShotTime DESC

		--Get Distinct values of the Pivot Column
		SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(SiteName)
		FROM (SELECT DISTINCT SiteName from #ActiveSessions) as SiteName	

		SELECT @ColumnName as SiteName

		SET @DynamicPivotQuery =
		N'
		SELECT ' + @ColumnName + ' as ' + @ColumnName + ', Region, LTRIM(RTRIM(FORMAT(sDate,''yyyy-MM-dd hh:00''))) as sDate
		FROM #ActiveSessions
		PIVOT (MAX(avgUsage)
			FOR SiteName IN (' + @ColumnName + ')) as PVTTable'
		
		PRINT @DynamicPivotQuery
		EXEC sp_executesql @DynamicPivotQuery

Open in new window


Output

SiteNames
[EMERPC01],[LONXD02],[LONXD03]

Table
EMERPC01      LONXD02      EMERPC01      LONXD02      LONXD03      Region      sDate
102      397      435      397      435      EMEA      2015-11-10 02:00
128      483      515      483      515      EMEA      2015-11-10 04:00
188      555      616      555      616      EMEA      2015-11-10 06:00
168      555      617      555      617      EMEA      2015-11-10 08:00
193      554      605      554      605      EMEA      2015-11-10 10:00
178      494      535      494      535      EMEA      2015-11-10 12:00
105      402      420      402      420      EMEA      2015-11-11 02:00
153      454      508      454      508      EMEA      2015-11-11 04:00
231      544      605      544      605      EMEA      2015-11-11 06:00
214      549      598      549      598      EMEA      2015-11-11 08:00
222      543      595      543      595      EMEA      2015-11-11 10:00
190      484      517      484      517      EMEA      2015-11-11 12:00
88      408      426      408      426      EMEA      2015-11-12 02:00
125      477      503      477      503      EMEA      2015-11-12 04:00
191      553      598      553      598      EMEA      2015-11-12 06:00
190      554      602      554      602      EMEA      2015-11-12 08:00
194      547      601      547      601      EMEA      2015-11-12 10:00
178      492      536      492      536      EMEA      2015-11-12 12:00
76      367      378      367      378      EMEA      2015-11-13 02:00
178      476      493      476      493      EMEA      2015-11-13 04:00
253      534      578      534      578      EMEA      2015-11-13 06:00
242      527      572      527      572      EMEA      2015-11-13 08:00
257      523      568      523      568      EMEA      2015-11-13 10:00
216      449      501      449      501      EMEA      2015-11-13 12:00
48      63      63      63      63      EMEA      2015-11-14 02:00
50      53      54      53      54      EMEA      2015-11-14 04:00
67      48      52      48      52      EMEA      2015-11-14 06:00
70      55      71      55      71      EMEA      2015-11-14 08:00
58      51      71      51      71      EMEA      2015-11-14 10:00
61      60      79      60      79      EMEA      2015-11-14 12:00
80      69      86      69      86      EMEA      2015-11-15 02:00
85      59      85      59      85      EMEA      2015-11-15 04:00
77      50      77      50      77      EMEA      2015-11-15 06:00
56      51      74      51      74      EMEA      2015-11-15 08:00
63      66      73      66      73      EMEA      2015-11-15 10:00
83      65      85      65      85      EMEA      2015-11-15 12:00

I'm filtering by region and I'm getting 2 extra columns not sure why....
LVL 4
Mauro CazabonnetSenior Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which ones are extra columns?
0
Mauro CazabonnetSenior Software EngineerAuthor Commented:
I expect to see what's listed in the sitenames which are
[EMERPC01],[LONXD02],[LONXD03] and region,date
0
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Data from ActiveConnections Table
SiteName      Region      sDate      avgUsage
EMERPC01      EMEA      2015-11-10 12:00:00.000      12
LONXD02      EMEA      2015-11-10 12:00:00.000      336
LONXD03      EMEA      2015-11-10 12:00:00.000      373
EMERPC01      EMEA      2015-11-10 02:00:00.000      45
LONXD02      EMEA      2015-11-10 02:00:00.000      351
LONXD03      EMEA      2015-11-10 02:00:00.000      382
EMERPC01      EMEA      2015-11-10 04:00:00.000      128
LONXD02      EMEA      2015-11-10 04:00:00.000      483
LONXD03      EMEA      2015-11-10 04:00:00.000      515
EMERPC01      EMEA      2015-11-10 06:00:00.000      188
LONXD02      EMEA      2015-11-10 06:00:00.000      555
LONXD03      EMEA      2015-11-10 06:00:00.000      616
EMERPC01      EMEA      2015-11-10 08:00:00.000      168
LONXD02      EMEA      2015-11-10 08:00:00.000      555
LONXD03      EMEA      2015-11-10 08:00:00.000      617
EMERPC01      EMEA      2015-11-10 10:00:00.000      193
LONXD02      EMEA      2015-11-10 10:00:00.000      554
LONXD03      EMEA      2015-11-10 10:00:00.000      605
EMERPC01      EMEA      2015-11-10 12:00:00.000      178
LONXD02      EMEA      2015-11-10 12:00:00.000      494
LONXD03      EMEA      2015-11-10 12:00:00.000      535
EMERPC01      EMEA      2015-11-10 02:00:00.000      102
LONXD02      EMEA      2015-11-10 02:00:00.000      397
LONXD03      EMEA      2015-11-10 02:00:00.000      435
EMERPC01      EMEA      2015-11-10 04:00:00.000      120
LONXD02      EMEA      2015-11-10 04:00:00.000      375
LONXD03      EMEA      2015-11-10 04:00:00.000      415
EMERPC01      EMEA      2015-11-10 06:00:00.000      72
LONXD02      EMEA      2015-11-10 06:00:00.000      361
LONXD03      EMEA      2015-11-10 06:00:00.000      387
EMERPC01      EMEA      2015-11-10 08:00:00.000      30
LONXD02      EMEA      2015-11-10 08:00:00.000      338
LONXD03      EMEA      2015-11-10 08:00:00.000      373
EMERPC01      EMEA      2015-11-10 10:00:00.000      13
LONXD02      EMEA      2015-11-10 10:00:00.000      325
LONXD03      EMEA      2015-11-10 10:00:00.000      374
EMERPC01      EMEA      2015-11-11 12:00:00.000      13
LONXD02      EMEA      2015-11-11 12:00:00.000      324
LONXD03      EMEA      2015-11-11 12:00:00.000      369
EMERPC01      EMEA      2015-11-11 02:00:00.000      28
LONXD02      EMEA      2015-11-11 02:00:00.000      342
LONXD03      EMEA      2015-11-11 02:00:00.000      377
EMERPC01      EMEA      2015-11-11 04:00:00.000      153
LONXD02      EMEA      2015-11-11 04:00:00.000      454
LONXD03      EMEA      2015-11-11 04:00:00.000      508
EMERPC01      EMEA      2015-11-11 06:00:00.000      231
LONXD02      EMEA      2015-11-11 06:00:00.000      544
LONXD03      EMEA      2015-11-11 06:00:00.000      605
EMERPC01      EMEA      2015-11-11 08:00:00.000      214
LONXD02      EMEA      2015-11-11 08:00:00.000      549
LONXD03      EMEA      2015-11-11 08:00:00.000      598
EMERPC01      EMEA      2015-11-11 10:00:00.000      222
LONXD02      EMEA      2015-11-11 10:00:00.000      543
LONXD03      EMEA      2015-11-11 10:00:00.000      595
EMERPC01      EMEA      2015-11-11 12:00:00.000      190
LONXD02      EMEA      2015-11-11 12:00:00.000      484
LONXD03      EMEA      2015-11-11 12:00:00.000      517
EMERPC01      EMEA      2015-11-11 02:00:00.000      105
LONXD02      EMEA      2015-11-11 02:00:00.000      402
LONXD03      EMEA      2015-11-11 02:00:00.000      420
EMERPC01      EMEA      2015-11-11 04:00:00.000      93
LONXD02      EMEA      2015-11-11 04:00:00.000      384
LONXD03      EMEA      2015-11-11 04:00:00.000      403
EMERPC01      EMEA      2015-11-11 06:00:00.000      46
LONXD02      EMEA      2015-11-11 06:00:00.000      372
LONXD03      EMEA      2015-11-11 06:00:00.000      389
EMERPC01      EMEA      2015-11-11 08:00:00.000      23
LONXD02      EMEA      2015-11-11 08:00:00.000      352
LONXD03      EMEA      2015-11-11 08:00:00.000      373
EMERPC01      EMEA      2015-11-11 10:00:00.000      11
LONXD02      EMEA      2015-11-11 10:00:00.000      340
LONXD03      EMEA      2015-11-11 10:00:00.000      370
EMERPC01      EMEA      2015-11-12 12:00:00.000      13
LONXD02      EMEA      2015-11-12 12:00:00.000      345
LONXD03      EMEA      2015-11-12 12:00:00.000      374
EMERPC01      EMEA      2015-11-12 02:00:00.000      35
LONXD02      EMEA      2015-11-12 02:00:00.000      357
LONXD03      EMEA      2015-11-12 02:00:00.000      383
EMERPC01      EMEA      2015-11-12 04:00:00.000      125
LONXD02      EMEA      2015-11-12 04:00:00.000      477
LONXD03      EMEA      2015-11-12 04:00:00.000      503
EMERPC01      EMEA      2015-11-12 06:00:00.000      191
LONXD02      EMEA      2015-11-12 06:00:00.000      553
LONXD03      EMEA      2015-11-12 06:00:00.000      598
EMERPC01      EMEA      2015-11-12 08:00:00.000      190
LONXD02      EMEA      2015-11-12 08:00:00.000      554
LONXD03      EMEA      2015-11-12 08:00:00.000      602
EMERPC01      EMEA      2015-11-12 10:00:00.000      194
LONXD02      EMEA      2015-11-12 10:00:00.000      547
LONXD03      EMEA      2015-11-12 10:00:00.000      601
EMERPC01      EMEA      2015-11-12 12:00:00.000      178
LONXD02      EMEA      2015-11-12 12:00:00.000      492
LONXD03      EMEA      2015-11-12 12:00:00.000      536
EMERPC01      EMEA      2015-11-12 02:00:00.000      88
LONXD02      EMEA      2015-11-12 02:00:00.000      408
LONXD03      EMEA      2015-11-12 02:00:00.000      426
EMERPC01      EMEA      2015-11-12 04:00:00.000      97
LONXD02      EMEA      2015-11-12 04:00:00.000      386
LONXD03      EMEA      2015-11-12 04:00:00.000      413
EMERPC01      EMEA      2015-11-12 06:00:00.000      68
LONXD02      EMEA      2015-11-12 06:00:00.000      374
LONXD03      EMEA      2015-11-12 06:00:00.000      393
EMERPC01      EMEA      2015-11-12 08:00:00.000      33
LONXD02      EMEA      2015-11-12 08:00:00.000      361
LONXD03      EMEA      2015-11-12 08:00:00.000      378
EMERPC01      EMEA      2015-11-12 10:00:00.000      18
LONXD02      EMEA      2015-11-12 10:00:00.000      356
LONXD03      EMEA      2015-11-12 10:00:00.000      380
EMERPC01      EMEA      2015-11-13 02:00:00.000      30
LONXD02      EMEA      2015-11-13 02:00:00.000      367
LONXD03      EMEA      2015-11-13 02:00:00.000      378
EMERPC01      EMEA      2015-11-13 04:00:00.000      178
LONXD02      EMEA      2015-11-13 04:00:00.000      476
LONXD03      EMEA      2015-11-13 04:00:00.000      493
EMERPC01      EMEA      2015-11-13 06:00:00.000      253
LONXD02      EMEA      2015-11-13 06:00:00.000      534
LONXD03      EMEA      2015-11-13 06:00:00.000      578
EMERPC01      EMEA      2015-11-13 08:00:00.000      242
LONXD02      EMEA      2015-11-13 08:00:00.000      527
LONXD03      EMEA      2015-11-13 08:00:00.000      572
EMERPC01      EMEA      2015-11-13 10:00:00.000      257
LONXD02      EMEA      2015-11-13 10:00:00.000      523
LONXD03      EMEA      2015-11-13 10:00:00.000      568
EMERPC01      EMEA      2015-11-13 12:00:00.000      216
LONXD02      EMEA      2015-11-13 12:00:00.000      449
LONXD03      EMEA      2015-11-13 12:00:00.000      501
EMERPC01      EMEA      2015-11-13 02:00:00.000      76
LONXD02      EMEA      2015-11-13 02:00:00.000      331
LONXD03      EMEA      2015-11-13 02:00:00.000      363
EMERPC01      EMEA      2015-11-13 04:00:00.000      55
LONXD02      EMEA      2015-11-13 04:00:00.000      298
LONXD03      EMEA      2015-11-13 04:00:00.000      340
EMERPC01      EMEA      2015-11-13 06:00:00.000      42
LONXD02      EMEA      2015-11-13 06:00:00.000      278
LONXD03      EMEA      2015-11-13 06:00:00.000      320
EMERPC01      EMEA      2015-11-13 08:00:00.000      19
LONXD02      EMEA      2015-11-13 08:00:00.000      254
LONXD03      EMEA      2015-11-13 08:00:00.000      303
EMERPC01      EMEA      2015-11-13 10:00:00.000      7
LONXD02      EMEA      2015-11-13 10:00:00.000      9
LONXD03      EMEA      2015-11-13 10:00:00.000      10
EMERPC01      EMEA      2015-11-14 12:00:00.000      6
LONXD02      EMEA      2015-11-14 12:00:00.000      12
LONXD03      EMEA      2015-11-14 12:00:00.000      13
EMERPC01      EMEA      2015-11-14 02:00:00.000      13
LONXD02      EMEA      2015-11-14 02:00:00.000      19
LONXD03      EMEA      2015-11-14 02:00:00.000      16
EMERPC01      EMEA      2015-11-14 04:00:00.000      50
LONXD02      EMEA      2015-11-14 04:00:00.000      43
LONXD03      EMEA      2015-11-14 04:00:00.000      34
EMERPC01      EMEA      2015-11-14 06:00:00.000      67
LONXD02      EMEA      2015-11-14 06:00:00.000      48
LONXD03      EMEA      2015-11-14 06:00:00.000      52
EMERPC01      EMEA      2015-11-14 08:00:00.000      70
LONXD02      EMEA      2015-11-14 08:00:00.000      55
LONXD03      EMEA      2015-11-14 08:00:00.000      71
EMERPC01      EMEA      2015-11-14 10:00:00.000      58
LONXD02      EMEA      2015-11-14 10:00:00.000      51
LONXD03      EMEA      2015-11-14 10:00:00.000      71
EMERPC01      EMEA      2015-11-14 12:00:00.000      61
LONXD02      EMEA      2015-11-14 12:00:00.000      60
LONXD03      EMEA      2015-11-14 12:00:00.000      79
EMERPC01      EMEA      2015-11-14 02:00:00.000      48
LONXD02      EMEA      2015-11-14 02:00:00.000      63
LONXD03      EMEA      2015-11-14 02:00:00.000      63
EMERPC01      EMEA      2015-11-14 04:00:00.000      41
LONXD02      EMEA      2015-11-14 04:00:00.000      53
LONXD03      EMEA      2015-11-14 04:00:00.000      54
EMERPC01      EMEA      2015-11-14 06:00:00.000      35
LONXD02      EMEA      2015-11-14 06:00:00.000      45
LONXD03      EMEA      2015-11-14 06:00:00.000      52
EMERPC01      EMEA      2015-11-14 08:00:00.000      19
LONXD02      EMEA      2015-11-14 08:00:00.000      42
LONXD03      EMEA      2015-11-14 08:00:00.000      49
EMERPC01      EMEA      2015-11-14 10:00:00.000      10
LONXD02      EMEA      2015-11-14 10:00:00.000      38
LONXD03      EMEA      2015-11-14 10:00:00.000      44
EMERPC01      EMEA      2015-11-15 12:00:00.000      7
LONXD02      EMEA      2015-11-15 12:00:00.000      37
LONXD03      EMEA      2015-11-15 12:00:00.000      41
EMERPC01      EMEA      2015-11-15 02:00:00.000      11
LONXD02      EMEA      2015-11-15 02:00:00.000      35
LONXD03      EMEA      2015-11-15 02:00:00.000      42
EMERPC01      EMEA      2015-11-15 04:00:00.000      24
LONXD02      EMEA      2015-11-15 04:00:00.000      44
LONXD03      EMEA      2015-11-15 04:00:00.000      49
EMERPC01      EMEA      2015-11-15 06:00:00.000      53
LONXD02      EMEA      2015-11-15 06:00:00.000      50
LONXD03      EMEA      2015-11-15 06:00:00.000      66
EMERPC01      EMEA      2015-11-15 08:00:00.000      56
LONXD02      EMEA      2015-11-15 08:00:00.000      51
LONXD03      EMEA      2015-11-15 08:00:00.000      74
EMERPC01      EMEA      2015-11-15 10:00:00.000      63
LONXD02      EMEA      2015-11-15 10:00:00.000      66
LONXD03      EMEA      2015-11-15 10:00:00.000      73
EMERPC01      EMEA      2015-11-15 12:00:00.000      83
LONXD02      EMEA      2015-11-15 12:00:00.000      65
LONXD03      EMEA      2015-11-15 12:00:00.000      85
EMERPC01      EMEA      2015-11-15 02:00:00.000      80
LONXD02      EMEA      2015-11-15 02:00:00.000      69
LONXD03      EMEA      2015-11-15 02:00:00.000      86
EMERPC01      EMEA      2015-11-15 04:00:00.000      85
LONXD02      EMEA      2015-11-15 04:00:00.000      59
LONXD03      EMEA      2015-11-15 04:00:00.000      85
EMERPC01      EMEA      2015-11-15 06:00:00.000      77
LONXD02      EMEA      2015-11-15 06:00:00.000      49
LONXD03      EMEA      2015-11-15 06:00:00.000      77
EMERPC01      EMEA      2015-11-15 08:00:00.000      27
LONXD02      EMEA      2015-11-15 08:00:00.000      37
LONXD03      EMEA      2015-11-15 08:00:00.000      59
EMERPC01      EMEA      2015-11-15 10:00:00.000      10
LONXD02      EMEA      2015-11-15 10:00:00.000      9
LONXD03      EMEA      2015-11-15 10:00:00.000      11
0
Arun MuruganCommented:
The below statement won't work.
SET @DynamicPivotQuery = N'SELECT ' + @ColumnName + ' as ' + @ColumnName + ', Region, LTRIM(RTRIM(FORMAT(sDate,''yyyy-MM-dd hh:00''))) as sDate FROM #ActiveSessions

Open in new window

Change it to;
SET @DynamicPivotQuery = N'SELECT ' + @ColumnName  + ', Region, LTRIM(RTRIM(FORMAT(sDate,''yyyy-MM-dd hh:00''))) as sDate FROM #ActiveSessions

Open in new window

The mistake is with @ColumnName + ' as ' + @ColumnName.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Awesome thx!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.