none
how to write dynamic query columns into rows in sql server RRS feed

  • Question

  • Hi I have one doubt in sql serve r

    how to combine  4 columns into 1 columns in sql server 

    Table : emp 
    empid |addr|sal|doj
    1     |hyd|10|10-01-1990
    2     |del|20|12-03-1999
    1     |pune|50|12-03-2017

    based on above data I want output like below

    empid |empvalues
    1     |hyd
    1     |10
    1     |10-01-1990
    2     |del
    2     |20
    2     |12-03-1999
    1     |pune
    1     |50
    1     |12-03-2017

    I tried like below :
    select empid,cast (addr as varchar(100)) as empvalues from emp
    union all
    select empid,cast (sal as varchar(100)) as empvalues from emp
    union all
    select empid,cast (doj as varchar(100)) as empvalues from emp
    above query is getting correct result.but query taking more time due to calling same tables 3 times.

    can you please tell me how to write any alternative query to achive this task in sql server .

    Thursday, January 2, 2020 4:11 PM

All replies

  • You can use cross apply or unpivot syntax to get what you want beside the union all method.

    CREATE TABLE mytable(
       empid INTEGER  NOT NULL  
      ,addr  VARCHAR(4)   NULL
      ,sal   INTEGER    NULL
      ,doj   DATE    NULL
    );
    INSERT INTO mytable(empid,addr,sal,doj) 
    VALUES (1,'hyd',10,'10-01-1990') 
    ,(2,'del',20,'12-03-1999') 
    ,(1,'pune',50,'12-03-2017');
    
    
    select empid,empvalues from mytable
    cross apply (values (cast (sal as varchar(100))) 
    ,(cast (addr as varchar(100)))
    ,(Format(doj,'MM-dd-yyyy')))   d(empvalues)
    
     
    drop  table mytable

    • Proposed as answer by Lily Lii Tuesday, January 7, 2020 8:25 AM
    Thursday, January 2, 2020 4:21 PM
    Moderator
  • DECLARE @Emp TABLE (
    	EmpId int,
    	Addr varchar(100),
    	Sal money,
    	dob date
    );
    INSERT INTO @Emp VALUES
    (1, 'abc', 10, '1990-01-01'),
    (2, 'def', 20, '1990-01-02'),
    (3, 'ghi', 30, '1990-01-03');
    
    SELECT EmpId, EmpValues
    FROM (
    	SELECT EmpId, Addr, CAST(Sal AS varchar(100)) AS Sal, CAST(dob AS varchar(100)) AS dob 
    	FROM @Emp
    ) AS p
    UNPIVOT (
    	EmpValues FOR Field IN (Addr, Sal, dob)
    ) AS unpvt;


    A Fan of SSIS, SSRS and SSAS

    • Proposed as answer by Lily Lii Tuesday, January 7, 2020 8:25 AM
    Thursday, January 2, 2020 5:23 PM
  • Hi jairama,

    When we need to carry out a simple convertion of columns into rows in SQL Server it is better to use UNPIVOT or VALUES structures.

    The UNPIVOT and VALUES ways have  been show above, they are more effective than UNION.

    For the tables, where different structure types might occur and the number of columns is unrestricted, it is recommended to use XML which unlike the dynamic SQL can be used inside the table functions.

    --use XML
    
    create table emp
    (empid int,
     addr varchar(10),
     sal int,
     doj date)
    
     insert emp values (1,'hyd',10,'10-01-1990'), 
                        (2,'del',20,'12-03-1999'), 
                        (1,'pune',50,'12-03-2017')
    
     select p.empid, 
            empvalues=t.c.value('.','varchar(10)')
     from(
          select empid,
    	         [XML]=( select addr,sal,doj
    			         for XML RAW('f'),type )
          from emp) p
    cross apply p.[XML].nodes('f/@*') t(c)
    

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Tuesday, January 7, 2020 8:25 AM
    Friday, January 3, 2020 5:32 AM
  • Hi jairama,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 7, 2020 8:26 AM
  • CREATE TABLE mytable(
       empid INTEGER  NOT NULL  
      ,addr  VARCHAR(4)   NULL
      ,sal   INTEGER    NULL
      ,doj   DATE    NULL
    );
    INSERT INTO mytable(empid,addr,sal,doj) 
    VALUES (1,'hyd',10,'10-01-1990') 
    ,(2,'del',20,'12-03-1999') 
    ,(1,'pune',50,'12-03-2017');
    
    
    --SQL Server 2016, 2017, 2019
    
     
      ;with mycte   
    (TheKey, TheValue) 
    as (
    SELECT [KEY], Value 
    from OpenJson((select * from mytable  FOR JSON AUTO ))
    WHERE type = 5
    )
    
     SELECT JSON_VALUE(src.TheValue,'$.empid') empid
     --,unpvt.[Key]  
     ,unpvt.Value  as empvalues
     FROM mycte AS src
     CROSS APPLY OpenJson(src.TheValue) AS unpvt  
     WHERE 
     unpvt.Type!=5  and
     --columns not in unpivot list
     unpvt.[Key] not in ('empid')
    
    drop table mytable


    Friday, January 17, 2020 8:18 PM
    Moderator