Converting Columns into rows with their respective data in sql server

Question

I have a scenario where I need to convert columns of table to rows  eg -  table - stocks   ScripName       ScripCode       Price    ----------------------------------------- 20 MICRONS      533022      39     I need to represent the table in the following format  but I just need this kind of representation for single row   ColName       ColValue ----------------------------- ScripName      20 MICRONS ScripCode      533022     Price          39   so that I can directly bind the data to the datalist control

User · Answer

As an alternative    Using CROSS APPLY and VALUES performs this operation quite simply and efficiently with just a single pass of the table  unlike union queries that do one pass for every column   SELECT     ca ColName  ca ColValue FROM YOurTable CROSS APPLY         Values            ScripName    ScripName              ScripCode    ScripCode              Price        cast Price as varchar 50         as CA  ColName  ColValue    Personally I find this syntax easier than using unpivot   NB  You must take care that all source columns are converted into compatible types for the single value column

User · Answer

Sound like you want to UNPIVOT  Sample from books online   --Create the table and insert values as portrayed in the previous example  CREATE TABLE pvt  VendorID int  Emp1 int  Emp2 int      Emp3 int  Emp4 int  Emp5 int   GO INSERT INTO pvt VALUES  1 4 3 5 4 4   INSERT INTO pvt VALUES  2 4 1 5 5 5   INSERT INTO pvt VALUES  3 4 3 5 4 4   INSERT INTO pvt VALUES  4 4 2 5 5 4   INSERT INTO pvt VALUES  5 5 1 5 5 5   GO --Unpivot the table  SELECT VendorID  Employee  Orders FROM      SELECT VendorID  Emp1  Emp2  Emp3  Emp4  Emp5    FROM pvt  p UNPIVOT     Orders FOR Employee IN         Emp1  Emp2  Emp3  Emp4  Emp5   AS unpvt  GO   Returns     VendorID   Employee   Orders ---------- ---------- ------ 1          Emp1       4 1          Emp2       3 1          Emp3       5 1          Emp4       4 1          Emp5       4 2          Emp1       4 2          Emp2       1 2          Emp3       5 2          Emp4       5 2          Emp5       5   see also  Unpivot SQL thingie and the unpivot tag

User · Answer

select  ScriptName   scriptName from table union all select  ScriptCode   scriptCode from table union all select  Price   price from table

User · Answer

DECLARE  TABLE TABLE     RowNo INT ScripName  VARCHAR 10  ScripCode  VARCHAR 10     Price  VARCHAR 10         INSERT INTO  TABLE VALUES    1  20 MICRONS    533022   39   SELECT ColumnName ColumnValue from  Table  Unpivot ColumnValue For ColumnName IN  ScripName ScripCode Price   AS H

User · Answer

CREATE TABLE  ORIGINAL       COUNTRY VARCHAR 50       MALE CRICKETER VARCHAR 50       FEMALE CRICKETER VARCHAR 50       MALE STAR VARCHAR 50       FEMALE STAR VARCHAR 50      select   from  ORIGINAL  SELECT COUNTRY  ca GENDER  ca STAR  ca CRICKETR FROM  ORIGINAL CROSS APPLY         Values            M   MALE CRICKETER  MALE STAR              F   FEMALE CRICKETER  FEMALE STAR       as CA  GENDER  CRICKETR  STAR

User · Answer

declare  T table  ScripName varchar 50   ScripCode varchar 50   Price int  insert into  T values   20 MICRONS    533022   39   select     ScripName  as ColName    ScripName as ColValue from  T union all select     ScripCode  as ColName    ScripCode as ColValue from  T union all select     Price  as ColName    cast Price as varchar 50   as ColValue from  T

User · Answer

i solved the query this way  SELECT     ca ID  ca  Name  FROM  Emp2  CROSS APPLY         Values            ID    cast ID as varchar                Name     Name       as CA  ID  Name    output look like    ID     Name ------ -------------------------------------------------- ID     1  Name  Joy ID     2  Name  jean ID     4  Name  paul

[sql-server] Converting Columns into rows with their respective data in sql server

Examples related to sql-server

Examples related to rows