Sql query help

  • Hi,

    below is the sample table with data and expected output

    Create table Sample1(ID int,value1 int);

    insert into Sample1

    select 1,100 union all select 1, 200 union all select 2,200 union all select 3,120

    union all select 3,20;

    Expected Result:

    Create table Sample2(Id int,value1 int, value2 int);

    insert into Sample2

    select 1,100,200 union all

    select 2,200,null union all

    select 3,120, 20

    select * from sample2;

    like this i have 1000's of data and i would like do this using direct insert query. Any suggestions how to achieve this. Basically i need to combine the rows based on id and insert into another table.

  • Can you explain the order of values in this query?

    select 1,100,200 union all

    select 2,200,null union all

    select 3,120, 20

    Why 100 goes before 200 for ID=1,

    and 120 goes before 20 for ID=3?

    Or it does not matter which comes first?

    _____________
    Code for TallyGenerator

  • Hi,

    yes, it doesn't match the order. is there any way to avoid the pivoting here.

  • KGJ-Dev (11/2/2016)


    Hi,

    is there any way to avoid the pivoting here.

    It depends.

    What is it for?

    What is 2nd table is made for?

    _____________
    Code for TallyGenerator

  • KGJ-Dev (11/2/2016)


    Hi,

    yes, it doesn't match the order. is there any way to avoid the pivoting here.

    If there are only 2 values, you could use MAX and MIN with a validation for second column (probably using NULLIF).

    If what you're trying to avoid is the pivot operator, then you could use cross tabs.

    SELECT ID,

    MAX( CASE WHEN rn = 1 THEN value1 END) AS value1,

    MAX( CASE WHEN rn = 2 THEN value1 END) AS value2

    FROM (

    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) rn FROM Sample1)x

    GROUP BY ID;

    SELECT ID,

    MIN(value1) AS value1,

    NULLIF( MAX(value1), MIN(value1)) AS value2

    FROM Sample1

    GROUP BY ID;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply