You must Sign In to post a response.
  • Category: SQL Server

    Columns value should be show in row style

    I have table like below
    id name came1 came2 came3 came 4
    1 A 1 4 0 0
    2 B 3 3 3 0
    3 C 2 2 1 1

    need query to display as below
    Id Name Cames
    1 A 1
    1 A 4
    2 B 3
    2 B 3
    2 B 3
    3 C 2
    3 C 2
    3 C 1
    3 C 2
    Please let me know any idea
    Thanks,
    Sankar
  • #762086
    select id,FirstName, came1 as came from t1 where came1 > 0 Union
    select id,FirstName, came2 as came from t1 where came2 > 0 Union
    select id,FirstName, came3 as came from t1 where came3 > 0 Union
    select id,FirstName, came4 as came from t1 where came4 > 0
    I got qyery like above is this correct or any easy method is there?

  • #762087
    Hi sankar,

    The above solution whatever you implement its not working in all scenario's. For example If you have columns 50 you can't write 50 select statements. The best way to do this is using "PIVOT" table, you can achieve it by using PIVOT table in SQL. Search in google how to write PIVOT Statements in SQL, and implement based on your need.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #762101
    Hi
    Sankar

    Your Query is Wrong Not Working which is you post

    Union not working use union all

    Check your C Element not correct from your post.

    try this Query working as your output


    Select * from t11
    select id,Name, came1 as came from t11 where came1 > 0 Union all
    select id,Name, came2 as came from t11 where came2 > 0 Union all
    select id,Name, came3 as came from t11 where came3 > 0 Union all
    select id,Name, came4 as came from t11 where came4 > 0
    order by Name



    I have attached Output Image refer them. Check and let me know

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment


Sign In to post your comments