July 16, 2009 at 5:12 am
Hi
I have an interesting problem that can be solved with multiple queries but would like to know if it's possible using a single query - I'm unsure if the PIVOT command can be bent to suit my needs though.
I have a table of users and couorses they have taken, with each course having a status.
Something like:
IDuseridcourse status
11Web 11
21Web 22
31Web 31
41Web 41
52Web 13
72Web 31
82Web 42
What I want to have returned is:
UserID Web1 Web2 Web3 Web4
1 1 2 1 1
2 3 NULL 1 2
Can this be done in a single query or do I need to go down the temp table route - by single query I mean some sort of Pivot syntax so I have a single SQL statement that lists the course as columns ... there will be a variable number of courses in the data table.
Any suggestions welcome,
Many thanks in advance
Steve
July 16, 2009 at 6:14 am
I hope this is what u are looking for
select UserId,
Sum(case when course = 'Web1' then Status else 0 end) web1,
Sum(case when course = 'Web2' then Status else 0 end) web2,
Sum(case when course = 'Web3' then Status else 0 end) web3,
Sum(case when course = 'Web4' then Status else 0 end) web4
from temptable
Group by UserId
NM
July 16, 2009 at 6:25 am
Here you go. Adapted one of my earlier queries to fit. Note that there's a few limitations - you can't have more than a certain number of columns in a table (can't remember how many that is though), and the maximum length of a column name is 255 characters, so you can't have course names longer than that.
The code might be a bit hard to read, so if you want, I can send it to you as a .SQL file.
Output:
UserIDWeb 1Web 2Web 3Web 4
11211
23NULL12
DECLARE @TestTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
UserID INT,
CourseName VARCHAR(MAX),
StatusID INT
)
INSERT INTO @TestTable (UserID, CourseName, StatusID)
SELECT 1, 'Web 1', 1
UNION
SELECT 1, 'Web 2', 2
UNION
SELECT 1, 'Web 3', 1
UNION
SELECT 1, 'Web 4', 1
UNION
SELECT 2, 'Web 1', 3
UNION
SELECT 2, 'Web 3', 1
UNION
SELECT 2, 'Web 4', 2
DECLARE @maxID INT
DECLARE @currentID INT
DECLARE @currentColumnName VARCHAR(MAX)
DECLARE @dynamicColumnString1 VARCHAR(MAX)
DECLARE @dynamicColumnString2 VARCHAR(MAX)
DECLARE @dynamicSQLString VARCHAR(MAX)
SET @maxID = 0
SET @currentID = 1
SET @currentColumnName = ''
SET @dynamicColumnString1 = ''
SET @dynamicColumnString2 = ''
SET @dynamicSQLString = ''
IF OBJECT_ID(N'tempdb..#ColumnNames ', N'U') IS NOT NULL
DROP TABLE #ColumnNames
CREATE TABLE #ColumnNames
(
counter INT IDENTITY PRIMARY KEY,
columnName VARCHAR(MAX)
)
INSERT INTO #ColumnNames (columnName)
SELECT DISTINCT CourseName
FROM @TestTable
ORDER BY CourseName
SELECT @maxID = MAX(counter) FROM #ColumnNames
WHILE @currentID <= @maxID
BEGIN
SELECT @currentColumnName = c.columnName
FROM #columnNames c
WHERE counter = @currentID
SET @dynamicColumnString1 = @dynamicColumnString1 + '[' + @currentColumnName + '] AS [' + @currentColumnName + '], '
SET @dynamicColumnString2 = @dynamicColumnString2 + '[' + @currentColumnName + '], '
SET @currentID = @currentID + 1
END
SET @dynamicColumnString1 = LEFT(@dynamicColumnString1, LEN(@dynamicColumnString1) - 1)
SET @dynamicColumnString2 = LEFT(@dynamicColumnString2, LEN(@dynamicColumnString2) - 1)
IF OBJECT_ID(N'tempdb..#userAnswers ', N'U') IS NOT NULL
DROP TABLE #userAnswers
CREATE TABLE #userAnswers
(
UserID INT,
StatusID INT,
CourseName VARCHAR(MAX)
)
INSERT INTO #userAnswers (UserID, StatusID, CourseName)
SELECT UserID, StatusID, CourseName
FROM @TestTable
SET @dynamicSQLString = @dynamicSQLString + 'SELECT UserID, ' + @dynamicColumnString1 + ' ' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + 'FROM ' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + '(SELECT UserID, StatusID, CourseName ' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + 'FROM #userAnswers) P ' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + 'PIVOT (MAX(StatusID) ' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + 'FOR CourseName IN (' + @dynamicColumnString2 + ')' + CHAR(13) + CHAR(10)
SET @dynamicSQLString = @dynamicSQLString + ') AS PVT'
EXEC (@dynamicSQLString)
DROP TABLE #ColumnNames
DROP TABLE #userAnswers
July 16, 2009 at 6:38 am
Thank you very much.
I knew there was a pivot in there somehwere but hadn't thought of dynamic sql 🙂
Thanks again
Steve
July 17, 2009 at 6:29 am
Try this......
DECLARE @TestTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
UserID INT,
CourseName VARCHAR(MAX),
StatusID INT
)
INSERT INTO @TestTable (UserID, CourseName, StatusID)
SELECT 1, 'Web 1', 1
UNION
SELECT 1, 'Web 2', 2
UNION
SELECT 1, 'Web 3', 1
UNION
SELECT 1, 'Web 4', 1
UNION
SELECT 2, 'Web 1', 3
UNION
SELECT 2, 'Web 3', 1
UNION
SELECT 2, 'Web 4', 2
select UserID,[Web 1],[Web 2],[Web 3],[Web 4]
from
(
select UserID,CourseNAme,StatusID
from @TestTable
) as A
Pivot
(
sum(statusID) FOR CourseName in([Web 1],[Web 2],[Web 3],[Web 4])
) AS Pivot_table
Thanks
Shailesh
July 17, 2009 at 7:03 am
Nazer and Shailesh, the problem with both of your ideas is that you assume he can hard-code the different course names in. If I understood the problem right, the reality is that the course names are varying, which means he would have to change his stored proc every time there was a new course, or an existing course was changed/removed.
July 17, 2009 at 10:20 am
for the static (i.e. all column names for output are know in advance) see http://www.sqlservercentral.com/articles/T-SQL/63681/ — this article also compares pivot solution with cross-tab — cross-tabs are easier to read/maintain and there is also favourable performance gain over pivots.
if it's the dynamic (not all columns/course names are know) see http://www.sqlservercentral.com/articles/cross+tab/65048/
///edit added cross-tab vs pivot rationale
July 17, 2009 at 10:52 am
Ya beat me to it. Thanks for the "plug", Allister. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2009 at 10:55 am
I ran through your post Jeff, I'm wondering if you're doing things any differently than the way I posted in my solution. I think I actually had been originally inspired by that post of yours, so it could be that it's the same solution, although yours seems to be quite a bit more developed.
July 17, 2009 at 11:06 am
Heh heh... np Jeff, any time, probs gonna be the only time I beat you to it... plugging one of your solutions!
@kramaswamy, your solution is based upon pivots, Jeff's first article, referenced above provides rationale why to use cross tab solution over pivot; second article provides dynamic cross tab solution.
July 17, 2009 at 11:20 am
Looks like I need to read it more carefully. I use Pivot tables all the time, so that might help to make some of my queries a bit more performant. Thanks!
July 19, 2009 at 7:21 pm
kramaswamy (7/17/2009)
I ran through your post Jeff, I'm wondering if you're doing things any differently than the way I posted in my solution. I think I actually had been originally inspired by that post of yours, so it could be that it's the same solution, although yours seems to be quite a bit more developed.
The other thing I'm doing differently is not using a While Loop. Other than that, probably much the same.
Thanks for the nod, too. I appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply