April 28, 2009 at 7:43 am
Hi
i have data in a table like this...
ct_code type gm_id gl_id
======================================================
201 D 1 NULL
201 T NULL 2
203 D 4 NULL
204 T NULL 4
205 T NULL 6
205 D 2 NULL
The output should be like this i.e combine rows on the basis ct_code.
I'm not able to write a single query to get the output like this.
That would be great if somebody can help me out.
ct_code gm_id gl_id
======================================
201 1 2
203 4 NULL
204 NULL 4
205 2 6111
Thanks
April 28, 2009 at 8:46 am
Will there only be two rows...If so could you use a subquery to look for the second instance?
April 28, 2009 at 8:55 am
I'm not sure how your data is in your table but I think this is what you looking for:
DECLARE @YourTable TABLE
(
ct_code INT,
type CHAR(1),
gm_id INT,
gl_id INT
)
INSERT INTO @YourTable
SELECT 201,'D', 1, NULL UNION ALL
SELECT 201,'T', NULL, 2 UNION ALL
SELECT 203,'D', 4, NULL UNION ALL
SELECT 204,'T', NULL, 4 UNION ALL
SELECT 205,'T', NULL, 6 UNION ALL
SELECT 205,'D', 2, NULL
SELECT
ct_code,
MAX(CASE WHEN Type = 'D' THEN gm_id END) as [gm_id],
MAX(CASE WHEN Type = 'T' THEN gl_id END )as [gl_id]
FROM @YourTable
GROUP BY ct_code
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply