How to display data from table 1 separately, and combined with matching rows from table 2Logging of incorrect...
SQL Server Service does not start automatically after system restart
Why do single electrical receptacles exist?
Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice
Do the speed limit reductions due to pollution also apply to electric cars in France?
Converting numbers to words - Python
Why does a single AND gate need 60 transistors?
Was the Spartan by Mimic Systems a real product?
How can I handle players killing my NPC outside of combat?
bash aliases do not expand even with shopt expand_aliases
Graphical modeler calculator missing in QGIS3
Are positive and negative energy from their respective planes inherently good and evil?
How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?
Sets which are both Sum-free and Product-free.
What is this mysterious *green square* on my Content Editor?
How to Build a List from Separate Lists
Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?
Buying a "Used" Router
Why write a book when there's a movie in my head?
Why is Shelob considered evil?
Why don't programs completely uninstall (remove all their files) when I remove them?
Multiple null checks in Java 8
What does @ mean in a hostname in DNS configuration?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
How do I purchase a drop bar bike that will be converted to flat bar?
How to display data from table 1 separately, and combined with matching rows from table 2
Logging of incorrect row in SQL Server Integration Serviceshow to select rows matching a condition with a defaultFind all potentially redundant records in a table using cursors and fuzzy string matchingCreating a view with a Left Outer Join and Where ConditionSubtracting sum of previous rows' data from current row's data in SQLHow to query two values from the same table from different records combined with fields from other tables?How to check values for records from different rowsHow to produce an output of fields that are NULL (combined from multiple columns+tables)?Adding extra total column that sums all column values on each rowUpdate columns with different values depending upon different condition of rows from scd2 type table
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.
For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?
Table t1: Table t2:
+----+----+ +----+----+----+
| i1 | x | | i2 | c1 | c2 |
+----+----+ +----+----+----+
| 1 | a | | 1 | 1 | |
| 2 | b | | 2 | 2 | g |
| 3 | c | | 3 | 3 | h |
| 4 | d | | 4 | 3 | i |
+----+----+ +----+----+----+
Result
+----+----+----+---+----+----+
| i | i1 | i2 | x |c1 | c2 |
+----+----+----+---+----+----+
| 1 | 1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2 | 1 | 1 | a | 1 |NULL|
| 3 | 2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4 | 2 | 2 | b | 2 | g |
| 5 | 3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6 | 3 | 3 | c | 3 | h |
| 7 | 3 | 4 | c | 3 | i |
| 8 | 4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+
NOTE: column i in the output is simply a row number.
sql-server sql-server-2014 view
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
add a comment |
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.
For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?
Table t1: Table t2:
+----+----+ +----+----+----+
| i1 | x | | i2 | c1 | c2 |
+----+----+ +----+----+----+
| 1 | a | | 1 | 1 | |
| 2 | b | | 2 | 2 | g |
| 3 | c | | 3 | 3 | h |
| 4 | d | | 4 | 3 | i |
+----+----+ +----+----+----+
Result
+----+----+----+---+----+----+
| i | i1 | i2 | x |c1 | c2 |
+----+----+----+---+----+----+
| 1 | 1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2 | 1 | 1 | a | 1 |NULL|
| 3 | 2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4 | 2 | 2 | b | 2 | g |
| 5 | 3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6 | 3 | 3 | c | 3 | h |
| 7 | 3 | 4 | c | 3 | i |
| 8 | 4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+
NOTE: column i in the output is simply a row number.
sql-server sql-server-2014 view
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
1
Please post what you've written so far in your attempts to create the view - we need to know how you're joiningT1toT2. Also, in your Result, there is a new column called 'i' - where did that come from?
– Scott Hodgin
Sep 26 '17 at 18:09
@ScottHodgin i am so sorry for not explaini, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution
– OnePage
Sep 26 '17 at 18:14
1
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I think there should be a 9th row containing the values9 | 4 | 4 | d | 3 | i |and row 7 should be7 | 3 | 3 | c | 3 | h |
– hot2use
Sep 27 '17 at 8:59
add a comment |
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.
For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?
Table t1: Table t2:
+----+----+ +----+----+----+
| i1 | x | | i2 | c1 | c2 |
+----+----+ +----+----+----+
| 1 | a | | 1 | 1 | |
| 2 | b | | 2 | 2 | g |
| 3 | c | | 3 | 3 | h |
| 4 | d | | 4 | 3 | i |
+----+----+ +----+----+----+
Result
+----+----+----+---+----+----+
| i | i1 | i2 | x |c1 | c2 |
+----+----+----+---+----+----+
| 1 | 1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2 | 1 | 1 | a | 1 |NULL|
| 3 | 2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4 | 2 | 2 | b | 2 | g |
| 5 | 3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6 | 3 | 3 | c | 3 | h |
| 7 | 3 | 4 | c | 3 | i |
| 8 | 4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+
NOTE: column i in the output is simply a row number.
sql-server sql-server-2014 view
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.
For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?
Table t1: Table t2:
+----+----+ +----+----+----+
| i1 | x | | i2 | c1 | c2 |
+----+----+ +----+----+----+
| 1 | a | | 1 | 1 | |
| 2 | b | | 2 | 2 | g |
| 3 | c | | 3 | 3 | h |
| 4 | d | | 4 | 3 | i |
+----+----+ +----+----+----+
Result
+----+----+----+---+----+----+
| i | i1 | i2 | x |c1 | c2 |
+----+----+----+---+----+----+
| 1 | 1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2 | 1 | 1 | a | 1 |NULL|
| 3 | 2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4 | 2 | 2 | b | 2 | g |
| 5 | 3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6 | 3 | 3 | c | 3 | h |
| 7 | 3 | 4 | c | 3 | i |
| 8 | 4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+
NOTE: column i in the output is simply a row number.
sql-server sql-server-2014 view
sql-server sql-server-2014 view
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
edited Sep 26 '17 at 18:30
RDFozz
9,89231531
9,89231531
asked Sep 26 '17 at 17:53
OnePageOnePage
104
asked Sep 26 '17 at 17:53
OnePageOnePage
104
asked Sep 26 '17 at 17:53
OnePageOnePage
104
104
1
Please post what you've written so far in your attempts to create the view - we need to know how you're joiningT1toT2. Also, in your Result, there is a new column called 'i' - where did that come from?
– Scott Hodgin
Sep 26 '17 at 18:09
@ScottHodgin i am so sorry for not explaini, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution
– OnePage
Sep 26 '17 at 18:14
1
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I think there should be a 9th row containing the values9 | 4 | 4 | d | 3 | i |and row 7 should be7 | 3 | 3 | c | 3 | h |
– hot2use
Sep 27 '17 at 8:59
add a comment |
1
Please post what you've written so far in your attempts to create the view - we need to know how you're joiningT1toT2. Also, in your Result, there is a new column called 'i' - where did that come from?
– Scott Hodgin
Sep 26 '17 at 18:09
@ScottHodgin i am so sorry for not explaini, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution
– OnePage
Sep 26 '17 at 18:14
1
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I think there should be a 9th row containing the values9 | 4 | 4 | d | 3 | i |and row 7 should be7 | 3 | 3 | c | 3 | h |
– hot2use
Sep 27 '17 at 8:59
1
1
Please post what you've written so far in your attempts to create the view - we need to know how you're joining
T1 to T2. Also, in your Result, there is a new column called 'i' - where did that come from?– Scott Hodgin
Sep 26 '17 at 18:09
Please post what you've written so far in your attempts to create the view - we need to know how you're joining
T1 to T2. Also, in your Result, there is a new column called 'i' - where did that come from?– Scott Hodgin
Sep 26 '17 at 18:09
@ScottHodgin i am so sorry for not explain
i, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution– OnePage
Sep 26 '17 at 18:14
@ScottHodgin i am so sorry for not explain
i, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution– OnePage
Sep 26 '17 at 18:14
1
1
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I think there should be a 9th row containing the values
9 | 4 | 4 | d | 3 | i | and row 7 should be 7 | 3 | 3 | c | 3 | h |– hot2use
Sep 27 '17 at 8:59
I think there should be a 9th row containing the values
9 | 4 | 4 | d | 3 | i | and row 7 should be 7 | 3 | 3 | c | 3 | h |– hot2use
Sep 27 '17 at 8:59
add a comment |
2 Answers
2
active
oldest
votes
Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?
CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO
CREATE VIEW TestView
AS
WITH cteInitialSet AS (
SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
UNION ALL
SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
FROM dbo.T1
JOIN dbo.T2 ON T1.i1 = T2.c1
)
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
i1 ,
i2 ,
x ,
c1 ,
c2
FROM cteInitialSet
GO
SELECT * FROM dbo.TestView;
answered Sep 26 '17 at 18:18
NicNic
3,618719
add a comment |
I know an answer was already picked over a year ago, and it's a good answer. But I was browsing and I saw this as a fun challenge to figure out, and I figured I might as well share my code since I took the time to write it.
Here's what I came up with. From what I can see, you essentially want some sort of a "header" row...so I used GROUPING SETS...It basically works the same way as @Nic's solution...but instead of doing a union, I'm duplicating the data with GROUPING SETS. Since you're not using any aggregates, the second grouping set just returns all records from t1 with everything else null.
So I started off by figuring out the two sets of data you want to combine:
--Thanks @Nic for the sample data :)
CREATE TABLE #T1 (i1 INT, x CHAR(1));
CREATE TABLE #T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO #T1 (i1, x) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
INSERT INTO #T2 (i2, c1, c2) VALUES (1,1,''), (2,2,'g'), (3,3,'h'), (4,3,'i');
GO
SELECT i = ROW_NUMBER() OVER (ORDER BY x.i1, x.i2, x.x)
, x.i1, x.i2, x.x, x.c1, x.c2
FROM (
SELECT DISTINCT t1.i1, t2.i2, t1.x, t2.c1, t2.c2
FROM #T1 t1
LEFT JOIN #T2 t2 ON t1.i1 = t2.c1
-- Data Rows Header Rows
GROUP BY GROUPING SETS ((t1.i1, t2.i2, t1.x, t2.c1, t2.c2), (t1.i1, t1.x))
) x
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f186955%2fhow-to-display-data-from-table-1-separately-and-combined-with-matching-rows-fro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?
CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO
CREATE VIEW TestView
AS
WITH cteInitialSet AS (
SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
UNION ALL
SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
FROM dbo.T1
JOIN dbo.T2 ON T1.i1 = T2.c1
)
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
i1 ,
i2 ,
x ,
c1 ,
c2
FROM cteInitialSet
GO
SELECT * FROM dbo.TestView;
answered Sep 26 '17 at 18:18
NicNic
3,618719
add a comment |
Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?
CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO
CREATE VIEW TestView
AS
WITH cteInitialSet AS (
SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
UNION ALL
SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
FROM dbo.T1
JOIN dbo.T2 ON T1.i1 = T2.c1
)
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
i1 ,
i2 ,
x ,
c1 ,
c2
FROM cteInitialSet
GO
SELECT * FROM dbo.TestView;
answered Sep 26 '17 at 18:18
NicNic
3,618719
add a comment |
Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?
CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO
CREATE VIEW TestView
AS
WITH cteInitialSet AS (
SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
UNION ALL
SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
FROM dbo.T1
JOIN dbo.T2 ON T1.i1 = T2.c1
)
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
i1 ,
i2 ,
x ,
c1 ,
c2
FROM cteInitialSet
GO
SELECT * FROM dbo.TestView;
answered Sep 26 '17 at 18:18
NicNic
3,618719
Your question is a little confusing (in particular around the joins), does this look something like what you are looking for?
CREATE TABLE T1 (i1 INT, x CHAR(1));
CREATE TABLE T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO T1 (i1, x) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
INSERT INTO T2 (i2, c1, c2) VALUES
(1,1,''),
(2,2,'g'),
(3,3,'h'),
(4,3,'i');
GO
CREATE VIEW TestView
AS
WITH cteInitialSet AS (
SELECT i1, NULL AS i2, x, NULL AS c1, NULL AS c2 FROM dbo.T1
UNION ALL
SELECT T1.i1, T2.i2, T1.x, T2.c1, T2.c2
FROM dbo.T1
JOIN dbo.T2 ON T1.i1 = T2.c1
)
SELECT ROW_NUMBER() OVER (ORDER BY i1, i2) AS i,
i1 ,
i2 ,
x ,
c1 ,
c2
FROM cteInitialSet
GO
SELECT * FROM dbo.TestView;
answered Sep 26 '17 at 18:18
NicNic
3,618719
answered Sep 26 '17 at 18:18
NicNic
3,618719
answered Sep 26 '17 at 18:18
NicNic
3,618719
answered Sep 26 '17 at 18:18
NicNic
3,618719
3,618719
add a comment |
add a comment |
I know an answer was already picked over a year ago, and it's a good answer. But I was browsing and I saw this as a fun challenge to figure out, and I figured I might as well share my code since I took the time to write it.
Here's what I came up with. From what I can see, you essentially want some sort of a "header" row...so I used GROUPING SETS...It basically works the same way as @Nic's solution...but instead of doing a union, I'm duplicating the data with GROUPING SETS. Since you're not using any aggregates, the second grouping set just returns all records from t1 with everything else null.
So I started off by figuring out the two sets of data you want to combine:
--Thanks @Nic for the sample data :)
CREATE TABLE #T1 (i1 INT, x CHAR(1));
CREATE TABLE #T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO #T1 (i1, x) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
INSERT INTO #T2 (i2, c1, c2) VALUES (1,1,''), (2,2,'g'), (3,3,'h'), (4,3,'i');
GO
SELECT i = ROW_NUMBER() OVER (ORDER BY x.i1, x.i2, x.x)
, x.i1, x.i2, x.x, x.c1, x.c2
FROM (
SELECT DISTINCT t1.i1, t2.i2, t1.x, t2.c1, t2.c2
FROM #T1 t1
LEFT JOIN #T2 t2 ON t1.i1 = t2.c1
-- Data Rows Header Rows
GROUP BY GROUPING SETS ((t1.i1, t2.i2, t1.x, t2.c1, t2.c2), (t1.i1, t1.x))
) x
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I know an answer was already picked over a year ago, and it's a good answer. But I was browsing and I saw this as a fun challenge to figure out, and I figured I might as well share my code since I took the time to write it.
Here's what I came up with. From what I can see, you essentially want some sort of a "header" row...so I used GROUPING SETS...It basically works the same way as @Nic's solution...but instead of doing a union, I'm duplicating the data with GROUPING SETS. Since you're not using any aggregates, the second grouping set just returns all records from t1 with everything else null.
So I started off by figuring out the two sets of data you want to combine:
--Thanks @Nic for the sample data :)
CREATE TABLE #T1 (i1 INT, x CHAR(1));
CREATE TABLE #T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO #T1 (i1, x) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
INSERT INTO #T2 (i2, c1, c2) VALUES (1,1,''), (2,2,'g'), (3,3,'h'), (4,3,'i');
GO
SELECT i = ROW_NUMBER() OVER (ORDER BY x.i1, x.i2, x.x)
, x.i1, x.i2, x.x, x.c1, x.c2
FROM (
SELECT DISTINCT t1.i1, t2.i2, t1.x, t2.c1, t2.c2
FROM #T1 t1
LEFT JOIN #T2 t2 ON t1.i1 = t2.c1
-- Data Rows Header Rows
GROUP BY GROUPING SETS ((t1.i1, t2.i2, t1.x, t2.c1, t2.c2), (t1.i1, t1.x))
) x
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I know an answer was already picked over a year ago, and it's a good answer. But I was browsing and I saw this as a fun challenge to figure out, and I figured I might as well share my code since I took the time to write it.
Here's what I came up with. From what I can see, you essentially want some sort of a "header" row...so I used GROUPING SETS...It basically works the same way as @Nic's solution...but instead of doing a union, I'm duplicating the data with GROUPING SETS. Since you're not using any aggregates, the second grouping set just returns all records from t1 with everything else null.
So I started off by figuring out the two sets of data you want to combine:
--Thanks @Nic for the sample data :)
CREATE TABLE #T1 (i1 INT, x CHAR(1));
CREATE TABLE #T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO #T1 (i1, x) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
INSERT INTO #T2 (i2, c1, c2) VALUES (1,1,''), (2,2,'g'), (3,3,'h'), (4,3,'i');
GO
SELECT i = ROW_NUMBER() OVER (ORDER BY x.i1, x.i2, x.x)
, x.i1, x.i2, x.x, x.c1, x.c2
FROM (
SELECT DISTINCT t1.i1, t2.i2, t1.x, t2.c1, t2.c2
FROM #T1 t1
LEFT JOIN #T2 t2 ON t1.i1 = t2.c1
-- Data Rows Header Rows
GROUP BY GROUPING SETS ((t1.i1, t2.i2, t1.x, t2.c1, t2.c2), (t1.i1, t1.x))
) x
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I know an answer was already picked over a year ago, and it's a good answer. But I was browsing and I saw this as a fun challenge to figure out, and I figured I might as well share my code since I took the time to write it.
Here's what I came up with. From what I can see, you essentially want some sort of a "header" row...so I used GROUPING SETS...It basically works the same way as @Nic's solution...but instead of doing a union, I'm duplicating the data with GROUPING SETS. Since you're not using any aggregates, the second grouping set just returns all records from t1 with everything else null.
So I started off by figuring out the two sets of data you want to combine:
--Thanks @Nic for the sample data :)
CREATE TABLE #T1 (i1 INT, x CHAR(1));
CREATE TABLE #T2 (i2 INT, c1 INT, c2 CHAR(1));
INSERT INTO #T1 (i1, x) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
INSERT INTO #T2 (i2, c1, c2) VALUES (1,1,''), (2,2,'g'), (3,3,'h'), (4,3,'i');
GO
SELECT i = ROW_NUMBER() OVER (ORDER BY x.i1, x.i2, x.x)
, x.i1, x.i2, x.x, x.c1, x.c2
FROM (
SELECT DISTINCT t1.i1, t2.i2, t1.x, t2.c1, t2.c2
FROM #T1 t1
LEFT JOIN #T2 t2 ON t1.i1 = t2.c1
-- Data Rows Header Rows
GROUP BY GROUPING SETS ((t1.i1, t2.i2, t1.x, t2.c1, t2.c2), (t1.i1, t1.x))
) x
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 11 mins ago
chadwinchadwin
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 11 mins ago
chadwinchadwin
11
answered 11 mins ago
chadwinchadwin
11
11
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
chadwin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f186955%2fhow-to-display-data-from-table-1-separately-and-combined-with-matching-rows-fro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Please post what you've written so far in your attempts to create the view - we need to know how you're joining
T1toT2. Also, in your Result, there is a new column called 'i' - where did that come from?– Scott Hodgin
Sep 26 '17 at 18:09
@ScottHodgin i am so sorry for not explain
i, it's just row number for seperate the values. I tried the all join commands but i didn't get any solution– OnePage
Sep 26 '17 at 18:14
1
I'm also confused why the last row in your result has a col-x of 'c' and why there doesn't seem to be a row in resut with a col-x of 'd'. What columns are you using to join these tables?
– Scott Hodgin
Sep 26 '17 at 18:17
I think there should be a 9th row containing the values
9 | 4 | 4 | d | 3 | i |and row 7 should be7 | 3 | 3 | c | 3 | h |– hot2use
Sep 27 '17 at 8:59