none
transfer rows to column RRS feed

  • Question

  • i want to transfer the rows to a single column below table is the actual data

    tbl_cp:

    id    cpname                   price        type

    1    Accessories                 3              10      
    1    Service Maintenance    5              NO-CHARGE
    2    Accracks                     2             1
    2    Accgear                      4              free

     

    below specified tabular format is the actual output .how to do this

    id       cpcombination

    1     Accessories,3,10;Service Maintenance,5, NO-CHARGE    

    2     Accracks,2,1;Accgear,4,free

     


    Sudhesh. G
    http://gurucoders.blogspot.com
    Monday, February 28, 2011 12:25 PM

Answers

  • Try

    Select distinct t1.id,
     Stuff((Select ';' + cpname + ',' + RTrim(Cast(Price As varchar(10))) + ',' + RTrim(type)
      From tbl_cp t2 Where t1.id = t2.id
      For XML Path('')),1,1,'') As cpCombination
    From tbl_cp t1;
    
    Tom

    • Marked as answer by Sudhesh.G Monday, February 28, 2011 1:20 PM
    Monday, February 28, 2011 12:56 PM

All replies

  • CREATE TABLE Foo (
      fookey INT NOT NULL PRIMARY KEY,
      y_n CHAR(1));

    INSERT INTO Foo VALUES(1, 'Y');
    INSERT INTO Foo VALUES(2, 'N');
    INSERT INTO Foo VALUES(3, 'Y');
    INSERT INTO Foo VALUES(4, 'Y');

    SELECT A.fookey, A.y_n,
           (SELECT '' + B.y_n
            FROM Foo AS B
            WHERE B.fookey <= A.fookey
            ORDER BY B.fookey
            FOR XML PATH('')) AS yn_path
    FROM Foo AS A;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 28, 2011 12:31 PM
    Answerer
  • Select distinct t1.id,
     Stuff((Select ';' + cpname + ',' + Cast(Price As varchar(10)) + ',' + type
      From tbl_cp t2 Where t1.id = t2.id
      For XML Path('')),1,1,'') As cpCombination
    From tbl_cp t1;
    
    Tom
    Monday, February 28, 2011 12:34 PM
  • thanks tom cooper it's working but i want to remove unwanted spaces in the    cpcombination col

    i got this out put

    id       cpcombination

     

    1        Accessories,10        ,3         ;Service Maintenance,NO-CHARGE ,5


    Sudhesh. G
    http://gurucoders.blogspot.com
    Monday, February 28, 2011 12:47 PM
  • Try

    Select distinct t1.id,
     Stuff((Select ';' + cpname + ',' + RTrim(Cast(Price As varchar(10))) + ',' + RTrim(type)
      From tbl_cp t2 Where t1.id = t2.id
      For XML Path('')),1,1,'') As cpCombination
    From tbl_cp t1;
    
    Tom

    • Marked as answer by Sudhesh.G Monday, February 28, 2011 1:20 PM
    Monday, February 28, 2011 12:56 PM
  • thank u cooper

    Sudhesh. G
    http://gurucoders.blogspot.com
    Monday, February 28, 2011 1:20 PM