Stored procedure to return table?

[jaybuffet]

Is there a SQL statement or a Stored Procedure that what return the following:

I have a table

num     text     text2
----------------------
1        a1        b1
5        a1        b2
8        a1        b3
2        a2        b1
4        a2        b2

And I want the results to be

name     b1     b2     b3
--------------------------
a1        1      5      8
a2        2      4      0

Is this possible?


Thanks
Jason

 

[LogicalmanUS]

Jason,

Here you go:

select text,
sum(case text2 when 'b1' then num else 0 end) AS B1,
sum(case text2 when 'b2' then num else 0 end) AS B2,
sum(case text2 when 'b3' then num else 0 end) AS B3
from tPIVOT group by text

(I named my table tPIVOT but the column headers were the same as per your question, with the same data)

If you want a further explanation, just ask.

Logicalman

 

[jaybuffet]

That would work potentially, but b1,b2,b3 are dynamic and can be any amount of columns up to 20

 

[LogicalmanUS]

Jason,

OK, no great problem there.

In that case you do need a SProc (as per your first suggestion) to return the results, as you will need to create a string for the TSQL statement, and then execute it.

Here goes, copy and paste it as a new SProc:

<CODE>
-- Written by Logicalman for TekTips 09/23/03
-- thread183-662264
-- This procedure takes a standard table and pivots the data as required
CREATE PROCEDURE [dbo].[usp_VARIABLE_PIVOTS]
AS
-- Declare the local variables
DECLARE @sSQL varchar(1000) --SQL String to execute
DECLARE @sCOL varchar (10) -- Column Name to process
-- Create the temp table to store pivoted headers
CREATE TABLE #tPIVOT
(
text2 varchar(10),
Flag int
)
-- Populate the temp table
INSERT #tPIVOT SELECT DISTINCT text2, 0 FROM tPIVOT ORDER BY text2

-- Prime Variable
SET @sSQL = ''

-- Get the first pivoted header
SET @sCOL = (SELECT TOP 1 text2 FROM #tPIVOT WHERE Flag=0)
-- while it's not null do something
WHILE NOT (@sCOL IS NULL)
BEGIN
-- append the new string to the local variable
SET @sSQL = @sSQL + ' sum(case text2 when ' + CHAR(39) + @sCOL + CHAR(39) + ' then num else 0 end) AS ' + @sCOL + ', '
-- update the temp table to stop repeating the same pivoted header
UPDATE #tPIVOT SET Flag = 1 WHERE text2 = @sCOL
-- get the next pivoted header
SET @sCOL = (SELECT TOP 1 text2 FROM #tPIVOT WHERE Flag=0)
END
-- Make sure we have a String to execute
IF LEN(@sSQL)>5
BEGIN
-- so add the SELECT, FROM and GROUP BY clauses
SET @sSQL = ' select text, ' + LEFT(@sSQL,LEN(@sSQL)-1) + ' from tPIVOT group by text'
-- OUTPUT
-- Execute the string
EXEC (@sSQL)
END

-- Drop the temp tables correctly
DROP TABLE #tPIVOT
GO

</CODE>

Logicalman