Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: grouping data

  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: grouping data

    some one plz help me.
    I had a table with these columns.
    Table(Id int,Name varchar,Value Varchar).
    I have to group them by ID and each Name becomes column name of the new table

    ex:-
    Id Name Value
    --------------------
    1 x a1
    2 x a2
    3 x a3
    1 y b1
    2 y b2
    3 y b3
    1 z c1
    2 z c2
    3 z c3

    I need it in this way

    x y z
    ------------
    a1 b1 c1
    a2 b2 c2
    a3 b3 c3


    (no of columns in the new table can't be pre determined)


    and which one would be better option to do this
    in VB.Net code or in a Storedprocedure?
    Last edited by theguru; 03-11-04 at 11:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can do this in a stored procedure using CASE statements. You can find a fine explanation of the method by searching for "Cross-Tab Reports" in Books online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Normally I'd recommend that you do this on the client, but it can (usually) be done on the server. Just to prove that, I wrote a snippet of code that seems to work, although it doesn't deal well with ill-behaved data. FWIW, my code is:
    PHP Code:
    CREATE TABLE tPivot (
       
    id        INT        NOT NULL
    ,  name        VARCHAR(20)    NOT NULL
    ,  value    VARCHAR(20)    NOT NULL
       
    )

    INSERT tPivot (idnamevalueVALUES (1'x''A1')
    INSERT tPivot (idnamevalueVALUES (2'x''A2')
    INSERT tPivot (idnamevalueVALUES (3'x''A3')

    INSERT tPivot (idnamevalueVALUES (1'y''B1')
    INSERT tPivot (idnamevalueVALUES (2'y''B2')
    INSERT tPivot (idnamevalueVALUES (3'y''B3')

    INSERT tPivot (idnamevalueVALUES (1'z''C1')
    INSERT tPivot (idnamevalueVALUES (2'z''C2')
    INSERT tPivot (idnamevalueVALUES (3'z''C3')

    DECLARE @
    cmd    NVARCHAR(4000)
    DECLARE @
    cName    SYSNAME

    SELECT 
    @cmd 'SELECT DISTINCT id'

    DECLARE zName CURSOR FOR SELECT DISTINCT
       name
       FROM tPivot

    OPEN zName
    FETCH zName INTO 
    @cName

    WHILE = @@fetch_status
       BEGIN
          SELECT 
    @cmd = @cmd 
    ',  (SELECT Min(value) FROM tPivot AS b'
    ' WHERE b.id = a.id'
    ' AND b.name = ''' + @cName 
    ''') AS [' + @cName ']'

          
    FETCH zName INTO @cName
       END

    CLOSE zName
    DEALLOCATE zName

    SELECT 
    @cmd = @cmd '   FROM tPivot AS a'

    SELECT @cmd
    EXECUTE 
    (@cmd
    Note that even though this CAN be done on the server, it would be better handled on the client side in most cases.

    -PatP
    Last edited by Pat Phelan; 03-11-04 at 15:12.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey Pat, you just love cursors, don't you?

    Code:
    declare @tbl table (
       ID int not null, 
       Name char(1) not null, 
       Value char(2) not null)
    insert @tbl values(1, 'x', 'a1') 
    insert @tbl values(2, 'x', 'a2')
    insert @tbl values(3, 'x', 'a3')
    insert @tbl values(1, 'y', 'b1')
    insert @tbl values(2, 'y', 'b2')
    insert @tbl values(3, 'y', 'b3')
    insert @tbl values(1, 'z', 'c1')
    insert @tbl values(2, 'z', 'c2')
    insert @tbl values(3, 'z', 'c3')
    
    select [x], [y], [z] from (
       select distinct ID from @tbl) t1
       left outer join (
          select ID, [x] = Value from @tbl where Name = 'x') t2
             on t1.ID = t2.ID
       left outer join (
          select ID, [y] = Value from @tbl where Name = 'y') t3
             on t1.ID = t3.ID
       left outer join (
          select ID, [z] = Value from @tbl where Name = 'z') t4
             on t1.ID = t4.ID

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rdjabarov
    Hey Pat, you just love cursors, don't you?
    Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Who loves left-joins and subqueries?

    declare @tbl table (
    ID int not null,
    Name char(1) not null,
    Value char(2) not null)
    insert @tbl values(1, 'x', 'a1')
    insert @tbl values(2, 'x', 'a2')
    insert @tbl values(3, 'x', 'a3')
    insert @tbl values(1, 'y', 'b1')
    insert @tbl values(2, 'y', 'b2')
    insert @tbl values(3, 'y', 'b3')
    insert @tbl values(1, 'z', 'c1')
    insert @tbl values(2, 'z', 'c2')
    insert @tbl values(3, 'z', 'c3')

    select max(case when Name = 'x' then Value end) as x,
    max(case when Name = 'y' then Value end) as y,
    max(case when Name = 'z' then Value end) as z
    from @tbl
    group by ID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: grouping data

    Originally posted by theguru
    (no of columns in the new table can't be pre determined)
    Was this part of the spec optional ???

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

    -PatP
    I saw the spec and stayed away....

    Blenderized data anyone?

    Salt or No Salt?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    Salt or No Salt?
    Stayed away ??? Don't you mean you're wasting away... oops, wait a sec, you're headed there anyway!

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not until 5:00 Pat....

    Hey, look at that...it's 5:00!

    See ya.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "(no of columns in the new table can't be pre determined)"

    Crap. Always read the fine print....

    theguru, I've seen posts for fully dynamic SQL code that will do on-demand cross tabs, though I haven't tested them. rdjabarov, didn't you have one? It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    [ready_to_kill_someone_for_a_cig]
    Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656??? Or even more realistic, like 2972??? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?
    [/ready_to_kill_someone_for_a_cig]

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by blindman
    It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released.
    At least I think that is what my code sample does. That is exactly why I had to resort to a cursor to build dynamic code, even though both of those go against my better judgement!

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whoa! Please go get your cig!

    People ask this question because
    A) They are trying to format the data for reporting
    and
    2) They were weaned on MS Access and its wonderfully convenient and fully dynamic cross-tab functionality.

    Problem is, theguru, that when you don't know the number of columns or the names of the columns, most reporting applications (such as Crystal or even MS Access' reports) will choke on the output.

    Perhaps your best bet would be to load the data into a pivot table in flat-file format, and then slice-and-dice however you want. How pretty does the output need to be?


    Pat Phelan, I like your idea, though I haven't tried it out. I'd call it semi-dynamic, since you are working with a defined table format. A fully-dynamic method applicable to any dataset is the Holy Grail of cross-tab reporting.
    Last edited by blindman; 03-11-04 at 18:45.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rdjabarov
    Isn't it indicative of both poor app and poor database design?
    I'm not prepared to argue that point. We'd both be "preaching to the choir" on this one!

    Originally posted by rdjabarov
    But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?
    What makes you think that the designer was in their sober mind ???

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •