August 14, 2015 at 4:49 am
hi,
CREATE TABLE A (ID INT IDENTITY (1,1))
CREATE TABLE B (ID INT, EMPID VARCHAR(10))
INSERT INTO A DEFAULT VALUES
GO 5
INSERT INTO B VALUES (1,'E23')
INSERT INTO B VALUES (1,'E24')
INSERT INTO B VALUES (2,'E23')
from the above code i would like to get output like
ID EMPID
1 23
2 23
3 null
4 null
5 null
1 24
2 null
3 null
4 null
5 null
I'm trying like
select a.id, b.empid from (
select * from a cross join (
select distinct empid from b) b
) a
left outer join b on a.id = b.id
but i get repetitive rows, can anyone throw some light?
Thanks and regards,
Ami
August 14, 2015 at 5:37 am
SELECT
a.ID,
b2.EMPID
FROM #a a
CROSS JOIN (SELECT DISTINCT id FROM #b) b1
LEFT JOIN (
SELECT ID, EMPID, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EMPID)
FROM #b
) b2 ON b2.id = a.id AND b2.rn = b1.id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2015 at 6:28 am
Hi,
I appreciate your response. I need little more info if I add some more records in B like
INSERT INTO #B VALUES (4,'E2')
INSERT INTO #B VALUES (5,'E2')
then the output should be like
ID EMPID
1 E23
2 E23
3 null
4 null
5 null
1 E24
2 null
3 null
4 null
5 null
1 null
2 null
3 null
4 E2
5 E2
Kind of, for every id in A it should give the corresponding value in B. if there are no values then it should return null.
Thanks and Regards,
Ami
August 14, 2015 at 7:11 am
Anamika (8/14/2015)
Hi,I appreciate your response. I need little more info if I add some more records in B like
INSERT INTO #B VALUES (4,'E2')
INSERT INTO #B VALUES (5,'E2')
then the output should be like
ID EMPID
1 E23
2 E23
3 null
4 null
5 null
1 E24
2 null
3 null
4 null
5 null
1 null
2 null
3 null
4 E2
5 E2
Kind of, for every id in A it should give the corresponding value in B. if there are no values then it should return null.
Thanks and Regards,
Ami
You have three sets in your result, each has five rows with ID 1 through 5. If you were to return the results in random order you would be lost. You need to introduce a new column into your results to distinguish between the three sets, so that you can order the result set to make sense of it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 14, 2015 at 7:25 am
Run this and you will see what I mean:
SELECT a.ID, b2.EMPID, Grp
FROM #a a
CROSS JOIN (
SELECT empid, Grp = ROW_NUMBER() OVER(ORDER BY MIN(ID)) FROM #b GROUP BY empid
) b1
LEFT JOIN #b b2 ON b2.ID = a.ID AND b2.empid = b1.empid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 15, 2015 at 1:06 am
TEST
August 15, 2015 at 1:08 am
IF OBJECT_ID(N'TAB1','U') IS NOT NULL
DROP TABLE TAB1;
CREATE TABLE TAB1 (id INT NOT NULL, num CHAR(3) NOT NULL);
IF OBJECT_ID(N'TAB2','U') IS NOT NULL
DROP TABLE TAB2;
CREATE TABLE TAB2 (id INT NOT NULL, num CHAR(3) NULL);
INSERT INTO TAB1 (id, num) VALUES
(1,'E23'),
(2,'E23'),
(1,'E24'),
(4,'E25'),
(5,'E25');
INSERT INTO TAB2(id) SELECT ROW_NUMBER() OVER(ORDER BY id) AS id
FROM TAB1 ;
SELECT B.id, A.num FROM (SELECT id, num FROM TAB1
WHERE num = 'E23') AS A
RIGHT OUTER JOIN
(SELECT TAB2.id FROM TAB2) AS B
ON A.id = B.id
UNION ALL
SELECT D.id, C.num FROM (SELECT id, num FROM TAB1
WHERE num = 'E24') AS C
RIGHT OUTER JOIN
(SELECT TAB2.id FROM TAB2) AS D
ON C.id = D.id
UNION ALL
SELECT F.id, E.num FROM (SELECT id, num FROM TAB1
WHERE num = 'E25') AS E
RIGHT OUTER JOIN
(SELECT TAB2.id FROM TAB2) AS F
ON E.id = F.id;
August 15, 2015 at 1:57 am
Here is one way of doing this, maybe not the most efficient but it works.
The method is almost identical to what Chris posted before and works with the sample data that ARROWW posted.
😎
;WITH GROUP_NUMS AS
(
SELECT DISTINCT
DENSE_RANK() OVER
(
ORDER BY T1.num
) AS GRNO
FROM dbo.TAB1 T1
)
SELECT
GN.GRNO
,T2.id
,TX.num
FROM dbo.TAB2 T2
CROSS APPLY GROUP_NUMS GN
LEFT OUTER JOIN
(
SELECT
T1.id
,DENSE_RANK() OVER
(
ORDER BY T1.num
) AS GRNO
,T1.num
FROM dbo.TAB1 T1
) AS TX
ON T2.id = TX.id
AND GN.GRNO = TX.GRNO
ORDER BY GN.GRNO
,T2.id;
Results
GRNO id num
------ ---- ----
1 1 E23
1 2 E23
1 3 NULL
1 4 NULL
1 5 NULL
2 1 E24
2 2 NULL
2 3 NULL
2 4 NULL
2 5 NULL
3 1 NULL
3 2 NULL
3 3 NULL
3 4 E25
3 5 E25
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply