August 10, 2019 at 3:36 am
Following is Table
+--------------+---------+--------------+
| Col1 | Col2 | Col3 |
+--------------+---------+--------------+
| PersonA | $10 | PersonB |
| PersonC | $20 | PersonD |
+--------------+--------+--------------+
i want result:
PersonA $10
PersonB $10
PersonC $20
PersonD $20
is there anyway without using union all?
August 10, 2019 at 2:13 pm
Like this?
Note: if you provide readily consumable data (create table and insert scripts), you're far more likely to get a tested answer.
-- STEP 1: set up some sample table(s)
CREATE TABLE #Data (
Person1 VARCHAR(10),
Amount SMALLMONEY,
Person2 VARCHAR(10)
);
GO
-- STEP 2: add some records
INSERT INTO #Data VALUES ('PersonA',10,'PersonB'),('PersonC',20,'PersonD');
-- STEP 3: write query
SELECT dv.Person, dv.Amt
FROM #Data d
CROSS APPLY (VALUES (d.Person1, Amount), (d.Person2, Amount)) dv(Person,Amt);
August 11, 2019 at 7:40 am
Thanks pietlinden
I want some modified result as below. Sorry for the changes. Please help with following.
+--------------+----------+-----------+
| Col1 | Dr | Cr |
+--------------+----------+-----------+
| PersonA | 0 | $10 |
| PersonB | $10 | 0 |
| PersonC | 0 | $20 |
| PersonD | $20 | 0 |
+--------------+----------+-----------+
August 11, 2019 at 8:31 am
i found the solution.
But one question:
how do i use this output (dv.person) for left join.
I need to get some information of dv.person from other table and for that i need left join of dv.person
August 11, 2019 at 2:13 pm
OUTER APPLY
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply