May 13, 2014 at 2:32 pm
Hi I am looking for a way to combine the following two tables to get a result set that would look like this:
1 ProjectOrange 2014-05-08 orange
1 update1
1 update2
1 update3
2 ProjectRed 2014-05-09 red
2 update1
2 update2
3 ProjectBlue 2014-05-10 blue
3 update1
3 update2
4 ProjectGreen 2014-05-11 green
4 update1
4 update2
If(OBJECT_ID('tempdb..#tbl_projects') Is Not Null)
Begin
Drop Table #tbl_projects
End
If(OBJECT_ID('tempdb..#tbl_updates') Is Not Null)
Begin
Drop Table #tbl_updates
End
Create table #tbl_projects(ProjectID int,Project_Name varchar(40),[Start_date] datetime, ProjectDescription varchar(40))
insert into #tbl_projects values
(1,'ProjectOrange',GETDATE()-5, 'orange'),
(2,'ProjectRed',GETDATE()-4, 'red'),
(3,'ProjectBlue',GETDATE()-3, 'blue'),
(4,'ProjectGreen',GETDATE()-2, 'green')
Create table #tbl_updates(ProjectID int, UpdateDescription varchar(40))
insert into #tbl_updates values
(1,'update1'),
(1, 'update2'),
(1, 'update3'),
(2,'update1'),
(2,'update2'),
(3,'update1'),
(3,'update2'),
(4,'update1'),
(4,'update2')
May 13, 2014 at 3:07 pm
You could use a UNION query and then just use NULL for the missing columns in the dataset with fewer columns. The sorting might be an issue. Hard to tell how that would work without seeing a real example. Maybe create a "phony" column that you use only for sorting.
Here's an attempt at it:
SELECT ProjectID
,Project_Name
,Start_Date
,ProjectDescription
, 1 AS OuterSort
FROM #tbl_projects
UNION ALL
SELECT ProjectID
, UpdateDescription
, NULL
, NULL
, 2
FROM #tbl_Updates
ORDER BY ProjectID, Project_Name, OuterSort;
The "OuterSort" column just allows me to sort on a column other than the ProjectName/StartDate. You may not need it.
May 13, 2014 at 3:10 pm
You could use a UNION query and then just use NULL for the missing columns in the dataset with fewer columns. The sorting might be an issue. Hard to tell how that would work without seeing a real example. Maybe create a "phony" column that you use only for sorting.
May 13, 2014 at 4:29 pm
SELECT
ProjectID,
Project_Name AS [Project_Name|UpdateDescription],
CONVERT(varchar(10), Start_date, 120) AS Start_date,
ProjectDescription
FROM #tbl_projects
UNION ALL
SELECT
ProjectID,
UpdateDescription,
'',
''
FROM #tbl_updates
ORDER BY
ProjectID,
3 DESC
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 13, 2014 at 4:35 pm
You could UNION the two together, but you'll have to have the text in two columns. So, how about we put the UNION query into a derived table. Something like this:
SELECT combo.ProjectID,
COALESCE(combo.Project_Name, combo.UpdateDescription)
FROM (SELECT tp.ProjectID,
tp.Project_Name,
tp.Start_date,
NULL AS 'UpdateDescription'
FROM #tbl_projects AS tp
UNION ALL
SELECT tu.ProjectID,
NULL AS 'Project_Name',
NULL AS 'Start_date',
tu.UpdateDescription
FROM #tbl_updates AS tu
) AS combo
ORDER BY combo.ProjectID ASC,
combo.Project_Name DESC;
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 13, 2014 at 4:38 pm
Sorry, it took me a little while to write that up and in the mean time others have posted roughly the same stuff. I kept getting distracted. Doggone twitter.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 14, 2014 at 6:14 am
Thank you to all for replying. Your responses have been extremely helpful! It's given me enough to work with.
.mf
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply