Pivot/Cross table
-
Following table contain data:
Custid Orid description
1 1 fa
1 1 ga
1 1 de
1 2 ba
1 2 aa
1 2 bb
1 2 cc
requested output as
custid orid desc1 desc2 desc3 desc4 desc 5
1 1 fa ga de
1 2 ba aa bb cc
I have tried to do it in various was but was not successful.
Please help.
Thanks
-
I for myself think, that this is not the job of the server, but rather the client.
However, several useful ideas you will get here
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Can be done by using same technique as in my reply to the thread
Flattening several rows into one row
Just replace the comma as a separator with a tab - or do whatever is needed to get the required spacing. Technique is only suitable for smallish tables.
By the way, thanks to others in the forum for finding out about those techniques in the first place.
-
I had the same problem and was able to find a stored procedure at this persons website.
http://www.johnmacintyre.ca/%5Ccodespct.asp
I have modified it some, but out of the box this will do what you are asking in a generic way. Additionally, the end result SQL can be printed and used to form a view.
Good Luck
-
If you search the scripts there are several methods...
CREATE PROCEDURE proCrossTabMulti
(
@strSELECT NVARCHAR(750), -- 'SELECT A,B'
@strFROM NVARCHAR(750), -- 'FROM atabletest INNERJOIN atbl1 ON atbl1.int1 = atabletest.int1'
@strWHERE NVARCHAR(750), -- 'WHERE (((A <> ''A'') AND (A <>''B'')) OR (B = ''C''))'
@strConvertField NVARCHAR(50), -- Field name to put at top strGroupI
@strTotals NVARCHAR(6) = 'NONE', -- Totals BOTTOM, RIGHT, BOTH, NONE, ALL
@strFields NVARCHAR(250), --Fields to Calc int1,int1,int2
@strFieldsAggregate NVARCHAR(250) -- sum,avg,count Aggregates for above @strFields number of arguments must match
 
AS
/************************************************************************************************
*** Developed by Chad Bowen 8/15/01
*** Executes a crosstab query that is similiar to Access PIVOT command
*** Converts on multiple fields
************************************************************************************************/
DECLARE @strReturn NVARCHAR(75)
DECLARE @strSQL NVARCHAR(4000)
DECLARE @intPosition1 INTEGER
DECLARE @intPosition2 INTEGER
DECLARE @strValu1 NVARCHAR(75)
DECLARE @strValu3 NVARCHAR(75)
DECLARE @intPosition3 INTEGER
DECLARE @intPosition4 INTEGER
SELECT name from sysobjects where name = 'TopTable'
IF @@ROWCOUNT > 0
BEGIN
DROP TABLE TopTable
END
CREATE TABLE TopTable
( strTop NVARCHAR(75))
SET @strSQL = 'INSERT INTO TopTable '
SET @strSQL = @strSQL + 'SELECT DISTINCT ' + @strConvertField -- CAST(' + @strConvertField + ' as NVARCHAR)'
SET @strSQL = @strSQL + ' ' + @strFROM
SET @strSQL = @strSQL + ' ' + @strWHERE
PRINT @strSQL
--INSERT INTO @TopTable
EXEC sp_executesql @strSQL
SET @strSQL = ''
SET @strSQL = @strSelect
DECLARE intCursor CURSOR FOR
SELECT * FROM topTable
OPEN intCursor
FETCH NEXT FROM intCursor INTO @strReturn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @intPosition1 = 1
SET @intPosition3 = 1
SET @intPosition2 = LEN(@strFields)
SET @intPosition4 = LEN(@strFieldsAggregate)
WHILE @intPosition2 <> 0
BEGIN
SET @intPosition2 = CHARINDEX(',',@strFields,@intPosition1)
SET @intPosition4 = CHARINDEX(',',@strFieldsAggregate,@intPosition3)
IF @intPosition2 = 0
BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,LEN(@strFields))
END
ELSE
BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,@intPosition2 - @intPosition1)
IF @intPosition2 = LEN(@strFields)
BEGIN
SET @intPosition2 = 0
END
END
IF @intPosition4 = 0
BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,LEN(@strFieldsAggregate))
END
ELSE
BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,@intPosition4 - @intPosition3)
IF @intPosition3 = LEN(@strFieldsAggregate)
BEGIN
SET @intPosition4 = 0
END
END
SET @intPosition3 = @intPosition4 + 1
SET @intPosition1 = @intPosition2 + 1
IF ((@intPosition2 = 0) AND (@intPosition4 <> 0)) OR ((@intPosition4 = 0) AND (@intPosition2<>0))
BEGIN
PRINT 'CONFLICTING # of Arguments'
RETURN
END
SET @strReturn = LTRIM(RTRIM(@strReturn))
SET @strSQL = @strSQL + ',' + @strValu3 + '(CASE '
SET @strSQL = @strSQL + @strConvertField + ' WHEN ' + CHAR(39) + @strReturn + CHAR(39)
SET @strSQL = @strSQL + ' THEN ' + @strValu1 + ' ELSE NULL END) AS ['
SET @strSQL = @strSQL + @strReturn + '-' + @strValu3 + '-' + @strValu1 + ']'
IF LEN(@strSQL) >=3999
BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
PRINT @strSQL
RETURN
END
END
FETCH NEXT FROM intCursor into @strReturn
END
CLOSE intCursor
DEALLOCATE intCursor
IF @strTotals = 'RIGHT' OR @strTotals = 'BOTH' OR @strTotals = 'ALL'
BEGIN
SET @intPosition1 = 1
SET @intPosition3 = 1
SET @intPosition2 = LEN(@strFields)
SET @intPosition4 = LEN(@strFieldsAggregate)
WHILE @intPosition2 <> 0
BEGIN
SET @intPosition2 = CHARINDEX(',',@strFields,@intPosition1)
SET @intPosition4 = CHARINDEX(',',@strFieldsAggregate,@intPosition3)
IF @intPosition2 = 0
BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,LEN(@strFields))
END
ELSE
BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,@intPosition2 - @intPosition1)
IF @intPosition2 = LEN(@strFields)
BEGIN
SET @intPosition2 = 0
END
END
IF @intPosition4 = 0
BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,LEN(@strFieldsAggregate))
END
ELSE
BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,@intPosition4 - @intPosition3)
IF @intPosition3 = LEN(@strFieldsAggregate)
BEGIN
SET @intPosition4 = 0
END
END
SET @intPosition3 = @intPosition4 + 1
SET @intPosition1 = @intPosition2 + 1
IF ((@intPosition2 = 0) AND (@intPosition4 <> 0)) OR ((@intPosition4 = 0) AND (@intPosition2<>0))
BEGIN
PRINT 'CONFLICTING # of Arguments'
RETURN
END
SET @strSQL = @strSQL + ',' + @strValu3 + '(' + @strValu1 + ') AS ['
SET @strSQL = @strSQL + @strValu3 + '-' + @strValu1 + ']'
IF LEN(@strSQL) >=3999
BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
PRINT @strSQL
RETURN
END
END
END
IF @strTotals = 'BOTTOM' OR @strTotals = 'BOTH' OR @strTotals = 'ALL'
BEGIN
SET @strSQL = @strSQL + ',' + 'GROUPING('
IF CHARINDEX ( ',', @strSELECT,0) > 0
BEGIN
SET @strSQL = @strSQL + SUBSTRING(@strSELECT,8,CHARINDEX ( ',', @strSELECT,0)-8) + ') ''grp'''
END
ELSE
BEGIN
SET @strSQL = @strSQL + SUBSTRING(@strSELECT,8,LEN(@strSELECT)) + ') ''grp'''
END
END
SET @strSQL = @strSQL + ' ' + @strFROM
SET @strSQL = @strSQL + ' ' + @strWHERE
SET @strSQL = @strSQL + ' GROUP BY ' + SUBSTRING(@strSELECT,8, LEN(@strSelect) - 7)
IF @strTotals = 'BOTTOM' OR @strTotals = 'BOTH'
BEGIN
SET @strSQL = @strSQL + ' WITH ROLLUP'
END
IF @strTotals = 'ALL'
BEGIN
SET @strSQL = @strSQL + ' WITH CUBE'
END
IF LEN(@strSQL) >=3999
BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
PRINT @strSQL
RETURN
END
PRINT @strSQL
SELECT * FROM TopTable
PRINT LEN(@strSQL)
EXEC sp_executesql @strSQL
RETURN
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply