You must Sign In to post a response.
  • Category: SQL Server

    SQL query for row span value

    Hi i have a table like this
    Id Colval
    1 a
    1 b
    1 c
    2 d
    2 e
    3 f
    4 g

    now i need output
    1 a,b,c
    2 d,e
    3 f
    4 g
  • #765557
    you can use PIVOT.
    PIVOT is one of the New relational operator introduced in Sql Server 2005. It provides an easy mechanism in Sql Server to transform rows into columns
    try out below query

    select * from table1 pivot Colval for id in (1,2,3,,4) as table1

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765560
    Hi Anita,

    PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

    So as per your requirement go for pivot table to get the desired output.

    Hope it helps!

  • #765566
    Hi Anitha

    Try this

    DECLARE @Data TABLE(Id int,CollVal varchar(10))
    insert into @Data values(1,'a'),(1,'b'),(1,'c')
    insert into @Data values(2,'d'),(2,'e')
    insert into @Data values(3,'f')
    insert into @Data values(4,'g')

    select * from @Data

    --Output
    select Id,stuff((select ', '+CollVal from @Data where Id=a.Id for xml path('')),1,2,'') Place
    from @Data a group by Id


Sign In to post your comments