Turning Rows into Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Turning Rows into Columns

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2015-05-07 : 15:27:45

I'm using SQL 2008.

I want to essentially turn rows into columns. The source table has a variable number of rows and a fixed number of columns - the magical, elusive SQL query will yield a result that has a variable number of columns and fixed number of rows. A slight twist is that there is grouping by Territory, and in this example the first two rows should be reduced to one, with the SlsPerson concatenated to AA/BB.

The table, represented by RC_DataTable:


Territory----State--Est--SlsPerson
----------------------------------
Chicago------IL-----2004--AA------
Chicago------IL-----2004--BB------
New York-----NY-----1989--CC------
Los Angeles--CA-----2007--DD------

The result of the query will yield:

COL1------COL2-------COL3----------
-----------------------------------
Chicago---New York---Los Angeles---
IL--------NY---------CA------------
2004------1989-------2007----------
AA/BB-----CC---------DD------------

Here is a script to establish the above data:



USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RCDataTable](
	[Territory] [varchar](20) NULL,
	[State] [varchar](2) NULL,
	[Established] [varchar](4) NULL,
	[Salesperson] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[RCDataTable] ([Territory], [State], [Established], [Salesperson]) VALUES (N'Chicago', N'IL', N'2004', N'AA')
INSERT [dbo].[RCDataTable] ([Territory], [State], [Established], [Salesperson]) VALUES (N'Chicago', N'IL', N'2004', N'BB')
INSERT [dbo].[RCDataTable] ([Territory], [State], [Established], [Salesperson]) VALUES (N'New York', N'NY', N'1989', N'CC')
INSERT [dbo].[RCDataTable] ([Territory], [State], [Established], [Salesperson]) VALUES (N'Los Angeles', N'CA', N'2007', N'DD')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-08 : 09:09:55

Good article on this here: http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql

BTW just how many rows? e.g. if all US cities, your result will have too many columns. Current max is 4096

https://msdn.microsoft.com/en-us/ms143432.aspx

Gerald Britton, MCSA
Toronto PASS Chapter

BobRoberts
Posting Yak Master

109 Posts

Posted - 2015-05-08 : 15:35:47

Thanks - I'll take a look at those links. How many rows? A good general question, but one I didn't address because it's not relevant to this particular task - the number of columns generated would rarely be above 5 and certainly never over 10.

quote:
Originally posted by gbritton

Good article on this here: http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql

BTW just how many rows? e.g. if all US cities, your result will have too many columns. Current max is 4096

https://msdn.microsoft.com/en-us/ms143432.aspx

Gerald Britton, MCSA
Toronto PASS Chapter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources