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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy