query result as header row
-
Hi Friday morning SQLers
intresting and also painful , I always seem to post when im slighlty hungover from the night before.
I have a select query which gives me
manger Id,Name,employee id
What Im after is trying to present this information
with all the manager Ids across the top and then underneath each manager Id I want a list of the employee ids
Is there a nicer way than using some crazy formulas in excel to get the desired result as excel is starting to eat at my system resources (400mb allocated) with the possibility of giving up on me but who knows what excel will do today
-
Always best done in presentation (app) but if you truly want to do it in sql then
DECLARE @maxemp int, @maxman int, @count int,@managerid int, @sql varchar(1000)
SELECT @maxemp = COUNT(DISTINCT employeeid) FROM #table
CREATE TABLE #result (colct int,rowid int)
SET @count = 0
WHILE (@count < @maxemp)
BEGIN
SET @count = @count + 1
INSERT INTO #result (colct,rowid) VALUES (0,@count)
END
CREATE TABLE #man (rowid int identity(1,1), managerid int)
INSERT INTO #man (managerid)
SELECT DISTINCT managerid FROM #table ORDER BY managerid
SET @maxman = SCOPE_IDENTITY()
SET @count = 0
WHILE (@count < @maxman)
BEGIN
SET @count = @count + 1
SELECT @managerid = managerid FROM #man WHERE rowid = @count
SET @sql = 'ALTER TABLE #result ADD ['+CAST(@managerid as varchar)+'] int'
EXEC(@sql)
CREATE TABLE #emp (rowid int identity(1,1), employeeid int)
INSERT INTO #emp (employeeid)
SELECT employeeid FROM #table
WHERE managerid = @managerid ORDER BY employeeid
SET @sql = 'UPDATE r SET colct=colct+1,['+
CAST(@managerid as varchar)+
'] = employeeid FROM #result r INNER JOIN #emp e ON e.rowid = r.rowid'
EXEC(@sql)
DROP TABLE #emp
END
SELECT * FROM #result WHERE colct > 0
DROP TABLE #result
DROP TABLE #man
However this has a limitation of 1022 managerid's (sql has 1024 column limit) but I presume this won't be a problem as you are using Excel
This would be a lot easier by writing an Excel macro.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply