October 15, 2007 at 12:30 pm
Let's say I have a table containing these values:
Id name title
--------------------------
1 Seba Developer
1 Joe DBA
1 Steve Developer
What should I do to get the similar output to this:
Id name title name title name title
---------------------------------------------------------------------
1 Seba Developer Joe DBA Steve Developer
I tried pivot function, but the problem is that it is very static, the number of persons per id can change, obviously names and titles will also vary from one row to the next. Any way to automate it through SQL?
October 15, 2007 at 12:44 pm
I wouldn't be very pretty, but you could use the following to find your maximum number of names and use some dynamic SQL to create your output. Depending on the size of your resultset this could get very complicated and long running Very Very fast. you may also think about creating a temp table and writing it out to there before you try to output it. Keep in mind that whatever your do to automate this coming from the database you'll also need the front end to handle and that could be much more difficult than getting the SQL written.
SELECT TOP 1 COUNT(*) AS MyMAX
FROM test
GROUP BY ID
ORDER BY COUNT(*) DESC
-Luke.
October 15, 2007 at 6:14 pm
Just curious... why do you have the same ID for 3 different people?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 6:47 pm
think about this id as a department id and not the identity for the user, so we can assume that 1 is a value from a lookup table, which could be represented as 'IT'.
October 16, 2007 at 12:06 am
Then, this should do it... not sure WHY you want to do this to perfectly good data, but it will do it...
[font="Courier New"]--drop table yourtable, #Scratchpad
--===== Presets
    SET NOCOUNT ON
--===== Create a sample data table. THIS IS NOT PART OF THE SOLUTION
 CREATE TABLE yourtable (ID INT, Name VARCHAR(10),Title VARCHAR(15))
 INSERT INTO yourtable (ID, Name, Title)
 SELECT '1','Seba','Developer' UNION ALL
 SELECT '1','Joe','DBA' UNION ALL
 SELECT '1','Steve','Developer' UNION ALL
 SELECT '2','Deb','Developer' UNION ALL
 SELECT '3','Dave','DBA' UNION ALL
 SELECT '3','James','Developer' UNION ALL
 SELECT '3','Venkat','DBA' UNION ALL
 SELECT '3','Bob','Developer'
--===== Copy the data into a temp table with room for an additional column
 SELECT ID, Name, Title, CAST(0 AS INT) AS SecondKey
   INTO #Scratchpad
   FROM yourtable
--===== This index is an absolute must to get the grouped running count
     -- for the SecondKey column to work
 CREATE CLUSTERED INDEX Composite ON #ScratchPad (ID,Title,Name)
--===== Declare some obvious named variables
DECLARE @PrevID INT
    SET @PrevID = 0
DECLARE @SecondCount INT
DECLARE @SQL0 VARCHAR(8000)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
--===== Create the grouped running count in the SecondKey column
 UPDATE #ScratchPad
    SET @SecondCount = SecondKey = CASE WHEN ID = @PrevID THEN @SecondCount+1 ELSE 1 END,
        @PrevID = ID
   FROM #ScratchPad WITH (INDEX(Composite),TABLOCKX)
--===== Create the necessary dynamic SQL
 SELECT @SQL0 = 'SELECT ID,'
 SELECT @SQL1 = ISNULL(@SQL1+',','')+CHAR(10) 
      + 'MAX(CASE WHEN SecondKey = ' + CAST(d.SecondKey AS VARCHAR(10)) + ' '
      + 'THEN Name  ELSE '''' END) AS Name'+ CAST(d.SecondKey AS VARCHAR(10)) + ','+CHAR(10)
      + 'MAX(CASE WHEN SecondKey = ' + CAST(d.SecondKey AS VARCHAR(10)) + ' '
      + 'THEN Title ELSE '''' END) AS Title' + CAST(d.SecondKey AS VARCHAR(10))
   FROM (SELECT DISTINCT SecondKey FROM #ScratchPad) d
  ORDER BY d.SecondKey
 SELECT @SQL2 = CHAR(10)+'FROM #ScratchPad GROUP BY ID'
--===== Execute the dynamic SQL
--PRINT @SQL0+@SQL1+@SQL2
   EXEC (@SQL0+@SQL1+@SQL2)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 6:25 am
October 16, 2007 at 9:02 am
They don't show if you copy and paste into Query Analyzer...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 9:08 am
They do on my machine.
Wait, do you mean Query Analyzer or Management Studio? They show in SSMS.
I can definitely see that Steve and the guys need to work on the formatting issues with the new version of this site. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 16, 2007 at 9:15 am
Thanks, the sql was a lot of help!
October 16, 2007 at 10:45 am
ISNULL(@SQL1+',','')+CHAR(10)
Just curious - what does this line of code do and how?
Noticed that if this is not there we just get one row but with this we get all the values for SECONDKEY
October 16, 2007 at 10:11 pm
Jason Selburg (10/16/2007)
They do on my machine.Wait, do you mean Query Analyzer or Management Studio? They show in SSMS.
I can definitely see that Steve and the guys need to work on the formatting issues with the new version of this site. 😀
Sorry Jason... I'm still using 2k and Query Analyzer... you would probably need to replace the non-breaking spaces with regular spaces...
... and, I agree... they need to make it so we can do some decent formatting of code with regular spaces and maybe even tabs.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 10:12 pm
My pleasure... thank you for the feedback...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2007 at 10:17 pm
SQLMAIN (10/16/2007)
ISNULL(@SQL1+',','')+CHAR(10)Just curious - what does this line of code do and how?
Noticed that if this is not there we just get one row but with this we get all the values for SECONDKEY
If @SQL1 is null, and it is for the first "row", it substitutes an empty string for the null so we can concatenate stuff to it including a linefeed character (CHAR(10)). If @SQL1 is not null, it adds a comma to the end of the current value of @SQL1 and then the linefeed... in both cases, the lines that follow that are concatenated to the current value of @SQL1... overall, it inserts the commas between rows without having an extra column at the end of the SELECT list that's being built. It's an old trick for building CSV columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply