none
how to merge three columns values to single row values in sql server 2008

    Question

  • Hi Frds.....

    I have three quantity in my table.

    Quantity1,quantity2,quantity3

    this three quantity have different values

    ex:

    quantity1 = 1000,quantity2=2000,quantity3=3000

    the three column combine 2 display in single row values. this values display in one by one.

    ex: quantity

         1000

         2000

         3000

    Friday, May 16, 2014 12:15 PM

Answers

  • You will need to use the UNPIVOT operator:

    DECLARE @example TABLE
    (
    	Id int NOT NULL IDENTITY(1,1),
    	Quantity1 int,
    	Quantity2 int,
    	Quantity3 int
    );
    
    INSERT INTO @example VALUES (1000, 2000, 3000), (4000, 5000, 6000);
    
    SELECT * FROM @example;
    
    SELECT Id, Quantity, QuantityType
    FROM @example
    UNPIVOT
    (
    	Quantity FOR QuantityType IN (Quantity1, Quantity2, Quantity3)
    ) AS u;

    Output:

    (2 row(s) affected)
    Id          Quantity1   Quantity2   Quantity3
    ----------- ----------- ----------- -----------
    1           1000        2000        3000
    2           4000        5000        6000
    
    (2 row(s) affected)
    
    Id          Quantity    QuantityType
    ----------- ----------- ---------------
    1           1000        Quantity1
    1           2000        Quantity2
    1           3000        Quantity3
    2           4000        Quantity1
    2           5000        Quantity2
    2           6000        Quantity3
    
    (6 row(s) affected)


    Friday, May 16, 2014 12:53 PM
  • SELECT col1, col2, ...
           CASE n.n WHEN 1 THEN Quantity1
                    WHEN 2 THEN Quantity2
                    WHEN 3 THEN Quantity4
           END
    FROM   tbl
    CROSS  APPLY (VALUES(1), (2), (3)) AS n(n)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 16, 2014 1:28 PM
  • There are multiple ways to do this

    1.UNPIVOT operator as shown in previous suggestion

    2. Using VALUES clause as below

    SELECT Qty
    FROM table t
    CROSS APPLY (VALUES (Quantity1),(Quantity2),(Quantity3))r(Qty)

    refer

    http://visakhm.blogspot.in/2012/05/multifacet-values-clause.html

    3. using UNION ALL

    SELECT Quantity1 AS Qty
    FROM Table
    
    UNION ALL
    
    SELECT Quantity2
    FROM Table
    
    UNION ALL
    
    SELECT Quantity3
    FROM Table
    


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 1:29 PM

All replies