June 20, 2011 at 10:18 am
Hi everybody I have the follow table CLI_NU:
DOC DDNTEL
00065955 NULL999434109
00065955 012952230
00065955 016171700
00091252 NULL3460852
00091252 NULL6108000
And this data have to show of the follow way:
DOC DDN1TEL1 DDN2TEL2DDN3TEL3
00065955 NULL999434109 012952230 016171700
00091252 NULL3460852 NULL 6108000 NULL NULL
With the command PIVOT I can't do it, I has try.
Please help me.
Thanks a lot
June 20, 2011 at 10:32 am
Are there always going to be at most three values for each DOC? If so, you can do this:
CREATE TABLE #CLI_NU
(
DOC VARCHAR(8),
DDN VARCHAR(2),
TEL VARCHAR(9)
)
INSERT INTO #CLI_NU (DOC, DDN, TEL)
VALUES ('00065955', NULL, '999434109')
INSERT INTO #CLI_NU (DOC, DDN, TEL)
VALUES ('00065955', '01', '2952230')
INSERT INTO #CLI_NU (DOC, DDN, TEL)
VALUES ('00065955', '01', '6171700')
INSERT INTO #CLI_NU (DOC, DDN, TEL)
VALUES ('00091252', NULL, '3460852')
INSERT INTO #CLI_NU (DOC, DDN, TEL)
VALUES ('00091252', NULL, '6108000')
WITH cte AS
(
SELECT DOC, DDN, TEL, ROW_NUMBER() OVER (PARTITION BY DOC ORDER BY DDN) AS rowNum
FROM #CLI_NU
)
SELECT
DOC,
(SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 1) AS DDN1,
(SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 1) AS TEL1,
(SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 2) AS DDN2,
(SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 2) AS TEL2,
(SELECT DDN FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 3) AS DDN3,
(SELECT TEL FROM cte c2 WHERE c1.DOC = c2.DOC AND rowNum = 3) AS TEL3
FROM cte c1
GROUP BY DOC
Of course, this is not very scalable. If you start getting large tables, having multiple queries against the same table will slow things down considerably. In that case you should probably reconsider the purpose and nature of this query.
June 20, 2011 at 10:45 am
Mi table have less of 3000 rows, your query is efficient.
Thanks by your help! You has safe me!
June 20, 2011 at 1:04 pm
Here's an alternative that should "slightly" outperform the previous solution...
It's the rather classic CrossTab approach (see the link in my signature for details).
;WITH cte AS
(
SELECT DOC, DDN, TEL, ROW_NUMBER() OVER (PARTITION BY DOC ORDER BY DDN) AS rowNum
FROM #CLI_NU
)
SELECT
DOC,
MAX(CASE WHEN rowNum = 1 THEN DDN ELSE NULL END) AS DDN1,
MAX(CASE WHEN rowNum = 1 THEN TEL ELSE NULL END) AS TEL1,
MAX(CASE WHEN rowNum = 2 THEN DDN ELSE NULL END) AS DDN2,
MAX(CASE WHEN rowNum = 2 THEN TEL ELSE NULL END) AS TEL2,
MAX(CASE WHEN rowNum = 3 THEN DDN ELSE NULL END) AS DDN3,
MAX(CASE WHEN rowNum = 3 THEN TEL ELSE NULL END) AS TEL3
FROM cte
GROUP BY DOC
June 20, 2011 at 1:43 pm
Hah wow. Yeah I was thinking about using that approach but for some reason decided it wouldn't work - of course it does. Thanks for correcting it 😛
June 20, 2011 at 2:06 pm
Thanks a lot to both! You are great!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply