locked
A transaction sql issue, Please see and suggest,,,,,,,, RRS feed

  • Question

  • There is a table

    ID        Grade

    1            a

    1           b

    2           a

    3          a

    3          b

    4          a

    4          b

     

    I want to retrive the data and output as

    ID      Grade

    1         a|b

    2          a

    3         a|b

    4         a|b

    It looks that the data can not be  [group by] and join the string  in the same group by any existing system function,

    We can use cursor to join those data by comparing the id

    Do you have any better solution?

    Thanks.

    Tuesday, September 21, 2010 1:49 AM

Answers

All replies

  • In SQL Server 2005 and up:

     

    select ID, stuff((select '|' + Grade from myTable T1 where T1.ID = T.ID order by Grade FOR XML PATH('')),1,1,'')
    
    from myTable T GROUP BY T.ID
    

     

    You can read a very good blog on this topic

    http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

     

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Dmitri KorotkevitchMVP Tuesday, September 21, 2010 2:06 AM
    • Marked as answer by Lin215 Tuesday, September 21, 2010 2:54 AM
    Tuesday, September 21, 2010 1:56 AM
  • Something like that

    declare
    	@T table
    	(
    		ID int not null,
    		Grade char(1) not null
    	)
    	
    insert into @T(ID, Grade)
    	select 1, 'a' union all
    	select 1, 'b' union all
    	select 2, 'a' union all
    	select 3, 'a' union all
    	select 3, 'b' union all
    	select 4, 'a' union all
    	select 4, 'b' 
    	
    ;with CTE(ID, Grades)
    as
    (
    	select 
    		ID,
    		convert(varchar(max),
    			(
    				select 
    					t2.Grade as [text()],
    					'|' as [text()]
    				from 
    					@T t2
    				where t2.ID = t1.ID
    				for xml path('')
    			)
    		) 
    	from
    		@T t1
    	group by
    		ID
    )
    select
    	ID,
    	LEFT(Grades,LEN(Grades) - 1)
    from 
    	CTE
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, September 21, 2010 2:02 AM
  • Thanks......

    Very good solution.

    Though not fast, but make code clean.

    Excellent

    Tuesday, September 21, 2010 2:55 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    What you want to do is violate First Normal Form (1NF). Then you want to do it with cursors. You have missed the fundamental concepts of RDBMS and a tiered architecture.  We do display formatting in the front end and never in the database.

    If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
    http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Tuesday, September 21, 2010 3:53 AM