Monthly Archives: June 2016

Change column value into column name (table flatening)

CREATE TABLE [dbo].[PivotExample](
[Country] [nvarchar](50) NULL,
[Year] [smallint] NOT NULL,
[SalesAmount] [money] NULL
)
GO

INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Australia’, 2005, 1309047)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Germany’, 2006, 521230.8475)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES(N’United States’, 2007, 2838512.3550)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’France’, 2008, 922179.0400)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Australia’, 2007, 3033784.2131)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’France’, 2005, 180571.6920)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United Kingdom’, 2006, 591586.8540)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Canada’, 2006, 621602.3823)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES(N’United Kingdom’, 2005, 291590.5194)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United States’, 2005, 1100549.4498)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Canada’, 2007, 535784.4624)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’France’, 2007, 1026324.9692)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Germany’, 2007, 1058405.7305)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Australia’, 2006, 2154284.8835)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United Kingdom’, 2008, 1210286.2700)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United States’, 2008, 3324031.1600)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Germany’, 2008, 1076890.7700)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United Kingdom’, 2007, 1298248.5675)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Australia’, 2008, 2563884.2900)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Canada’, 2005, 146829.8074)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Germany’, 2005, 237784.9902)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’Canada’, 2008, 673628.2100)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’United States’, 2006, 2126696.5460)
INSERT [dbo].[PivotExample] ([Country], [Year], [SalesAmount]) VALUES (N’France’, 2006, 514942.0131)
GO

SELECT * FROM [dbo].[PivotExample] ORDER BY Country
GO

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

select @cols = STUFF((SELECT ‘,’ + QUOTENAME(Country)
from PivotExample
group by Country
order by Country
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)

print @cols
set @query = N’SELECT [Year],’ + @cols + N’ from
[dbo].[PivotExample]
pivot
(
SUM(SalesAmount)
for Country in (‘ + @cols + N’)
) p ‘
print @query
exec sp_executesql @query;