November 1, 2016 at 6:32 pm
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.
November 1, 2016 at 10:56 pm
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
November 2, 2016 at 5:09 am
Hi,
yes, it doesn't match the order. is there any way to avoid the pivoting here.
November 2, 2016 at 5:45 am
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
November 2, 2016 at 8:56 am
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;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply