locked
Columns unique value to header names in single query or nested RRS feed

  • Question

  • User-1155576298 posted

    Please Help!!

    Need sql query in sql server 2008 r2

    Need sql query for below 

    My Table structure

    Name     Data

    X               10

    x1              11

    x2              22

    x                22

    x1              23

    x2              24

    Need result as

    ________________________________________

    x        x1        x2

    ________________________________________

    10      11        22

    22      23        24

    __________________________________________

    Thanks in advance

    Saturday, February 17, 2018 11:23 AM

All replies

  • Monday, February 19, 2018 9:40 AM
  • User726159118 posted

    Hi AdvancedSQl,

    Refer below query to generate the out put as you mentioned in the question.

    select *
    from 
    (
      select Data
      from yourTable
    ) x
    pivot
    (
      max(Data)
      for property in ([X ], [X1], [X2])
    ) p

    Mark As Answer If it is helpful.

    Omkar.

    Monday, February 19, 2018 9:53 AM
  • User-1155576298 posted

    Thanks Bro. for replying

    but as you mentioned the query which m running is returning only 1 row which is max row from my data table.

    Below is my query which is returning only single row

    select * from ( select DName, datas from DataTable ) x
    pivot
    (
    max(datas)
    for dname in ([X ], [X1], [X2])
    ) p

    m want output for all values of x, x1, x2 as i have mentioned above in my output

    Plz help

    Thanks in advance

    Monday, February 19, 2018 10:08 AM
  • User-1155576298 posted

    Thanks for replying

    m already gone through those articles but not much helpful!!!

    Anyways thanks

    Monday, February 19, 2018 11:00 AM
  • User726159118 posted

    Welcome,


    Refer below sql script using this i am getting expected output. I have used row number to make each name as separate.

    create table #tableVar
    (
    [Name]      varchar(10),
    [data] int
    )
    
    insert into #tableVar ( [Name] ,[data]  ) values  ('X', 10)
    insert into #tableVar ( [Name] ,[data]  )values ('X2', 11)
    insert into #tableVar ( [Name] ,[data]  ) values ('X3', 22)
    insert into #tableVar ( [Name] ,[data]  ) values ('X', 23)
    insert into #tableVar ( [Name] ,[data]  ) values  ('X2', 24)
    insert into #tableVar ( [Name] ,[data]  ) values  ('X3', 24)
    
    
    
    
    
    
    
    select *
    from 
    (
      select ROW_NUMBER() OVER(Partition by name order by Name) As rownumber, Data,Name
      from #tableVar
    ) As x
    pivot
    (
      max(data)
      for Name in ([X ], [X2], [X3])
    ) As p
    
    
    
    Drop  table #tableVar

    Mark As Answer If it is helpful.

    Omkar.


    Monday, February 19, 2018 12:01 PM
  • User77042963 posted
    CREATE TABLE mytable( Name varchar(10),data int)
    
    insert into mytable (Name,data ) values  
    ('X', 10),('X1', 11),('X2', 22)
    ,('X', 23),('X1', 24),('X2', 24)
    
    ;with mycte as (
     Select  *, Row_Number() Over(Partition by Name Order by data) rn 
     from  mytable)
    
     Select max(Case when Name='x' then data Else null End) 'x'
     ,max(Case when Name='x1'  then data Else null End) 'x1'
     ,max(Case when  Name='x2'  then data Else null End) 'x2'
     from mycte
     Group by rn 
    
    
    
     drop  TABLE mytable
    

    Monday, February 19, 2018 2:58 PM