Transpose a table - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Transpose a table

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-22 : 09:58:25

Hi I am having a table monthsales
columns
month sales
jan 100
feb 90
mar 80
apr 70

now i want to get it in the format
jan feb mar apr
100 90 80 70

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-22 : 10:33:03

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-22 : 10:54:03

Also reada about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-23 : 06:06:46

Check
select
sum (case month1 when 'Jan' then sales end) as Jan,
sum (case month1 when 'Feb' then sales end) as Feb,
sum (case month1 when 'Mar' then sales end) as Mar,
sum (case month1 when 'Apr' then sales end) as Apr

from monthsales

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 07:37:20

dynamic way of doing that..


Create table Tmp
(
	TransDate 	int,
	Amount		int
)
insert into Tmp
select	2006001, 1000	union all
select	2006001, 1200	union all
select	2006001, 1300	union all
select	2006002, 1200	union all
select	2006002, 2600	union all
select	2006002, 9800	union all
select	2006002, 3540	union all
select	2006003, 1200	union all
select	2006003, 2200	union all
select	2006003, 3982	union all
select	2006003, 1200 

--Select * From @Table

Declare @sQry Varchar(8000)

Declare @date int 
Select @Date = 0,@sQry = 'Select * , '

While @Date < (Select Distinct Max(TransDate) From  Tmp )
Begin 
Select @Date = Min(TransDate) From Tmp Where TransDate > @Date
Select @sQry = @sQry + ' Case When TransDate = ' + Convert(varchar,@Date) +  ' Then Amount Else 0 End As [' + Convert(varchar,@Date) + '],'
End 

Select @sQry = left(@sQry,len(@sQry)-1)
Select @sQry = @sQry + ' From Tmp'
Exec(@sQry)

Drop Table Tmp

Chirag

SimonH_UK
Starting Member

1 Post

Posted - 2010-03-25 : 16:33:13

Best to limit to small scale for this sort of thing. If you're using SQL 2k though and don't have PIVOT features available, I've drafted a stored proc that should do the job for you. Bit of a botch rush job so pull it apart as much as you like. Paste the below into a sql window and edit the EXEC at the bottom as preferred. If you want to see what's being generated, remove the --s in the middle:

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'USP_LIST_CONCAT')
DROP PROCEDURE USP_LIST_CONCAT
GO

CREATE PROCEDURE USP_LIST_CONCAT (@SourceTable NVARCHAR(1000) = '' ,@SplitColumn NVARCHAR(1000) = '' , @Deli NVARCHAR(10) = '', @KeyColumns NVARCHAR(2000) = '' , @Condition NVARCHAR(1000) = '')
AS
BEGIN
SET NOCOUNT ON

/* PROCEDURE CREATED 2010 FOR SQL SERVER 2000. SIMON HUGHES. */
/* NOTES: REMOVE --'s BELOW TO LIST GENERATED SQL. */

IF @SourceTable = '' OR @SourceTable = '?' OR @SourceTable = '/?' OR @SplitColumn = '' OR @KeyColumns = ''
BEGIN
PRINT 'Format for use:'
PRINT ' USP_LIST_CONCAT ''SourceTable'', ''SplitColumn'', ''Deli'', ''KeyColumn1,...'', ''Column1 = 12345 AND ...'''
PRINT ''
PRINT 'Description:'
PRINT 'The SourceTable should contain a number of records acting as a list of values.'
PRINT 'The SplitColumn should be the name of the column holding the values wanted.'
PRINT 'The Delimiter may be any single character or string ie ''/'''
PRINT 'The KeyColumn may contain a comma seperated list of columns that will be returned before the concatenated list.'
PRINT 'The optional Conditions may be left blank or may include the following as examples:'
PRINT ' ''Column1 = 12334 AND (Column2 = ''ABC'' OR Column3 = ''DEF'')'''
PRINT ''
PRINT 'A standard list in the format:'
PRINT ' Store1, Employee1, Rabbits'
PRINT ' Store1, Employee1, Dogs'
PRINT ' Store1, Employee1, Cats'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'Will be returned as:'
PRINT ' Store1, Employee1, Cats/Dogs/Rabbits'
PRINT ' Store1, Employee2, Dogs'
PRINT ''
PRINT 'A full ORDER BY and DISTINCT is included'
RETURN -1
END

DECLARE @SQLStatement NVARCHAR(4000)

SELECT @SQLStatement = '
DECLARE @DynamicSQLStatement NVARCHAR(4000)

SELECT @DynamicSQLStatement = ''SELECT '+@KeyColumns+', SUBSTRING(''

SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' + '' + CHAR(10) +
'' MAX(CASE WHEN '+@SplitColumn+' = ''''''+RTRIM('+@SplitColumn+')+'''''' THEN '''''+@Deli+'''+RTRIM('+@SplitColumn+')+'''''' ELSE '''''''' END)''
FROM '+ @SourceTable +' ORDER BY '+@SplitColumn+'

SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' ,2,7999) List'' + CHAR(10) + ''FROM '+ @SourceTable+''' + CHAR(10) +'''+CASE WHEN @Condition = '' THEN '/* WHERE */' ELSE 'WHERE '+@Condition END+ '''+ CHAR(10) + ''GROUP BY '+@KeyColumns+'''

SELECT @DynamicSQLStatement = REPLACE(@DynamicSQLStatement,''( +'',''('')

-- SELECT @DynamicSQLStatement -- DEBUG ONLY
EXEC (@DynamicSQLStatement)'

EXEC (@SQLStatement)

END
GO

EXEC USP_LIST_CONCAT 'MyTableName', 'ColumnForListing', 'Delimiter', 'KeyCol1, KeyCol2', 'Column1 = 123456'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources