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:
1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red
2update1
2update2
3ProjectBlue2014-05-10 blue
3update1
3update2
4ProjectGreen 2014-05-11 green
4update1
4update2
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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