Query to combine two tables based a third table!!
-
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
Thanks in advance!
-
Hi and welcome to the forums! You can do this using STUFF. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
If you need help with the coding part please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
to get one columns a s a comma delimited, one to many, you'll want to use the FOR XML trick.
if you provide actual cREATE TABLe / INSERT INTO code that we can use to test with, we could provide a tested solution.
here's two examples of using FOR XML, that you'd want to try and adapt:
SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
declare @skills table (Resource_Id int, Skill_Id varchar(20))
insert into @skills
select 101, 'sqlserver' union all
select 101, 'vb.net' union all
select 101, 'oracle' union all
select 102, 'sqlserver' union all
select 102, 'java' union all
select 102, 'excel' union all
select 103, 'vb.net' union all
select 103, 'java' union all
select 103, 'oracle'
---
select * from @skills s1
--- Concatenated Format
set statistics time on;
SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id
FROM @skills s2
WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below
ORDER BY Skill_Id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM @skills s1
GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned
ORDER BY s1.Resource_Id
set statistics time off;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is my script to create the 3 tables, thanks a lot!!!!
USE [Sample]
GO
/****** Object: Table [dbo].[TableA] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[PhoneNumber] [int] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TableB] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableB](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Task] [varchar](50) NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TableC] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[A's Id] [int] NOT NULL,
[B's Id] [int] NOT NULL,
CONSTRAINT [PK_TableC] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TableA] ON
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (1, N'aaa', N'bbb', 1111)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (2, N'ccc', N'ddd', 2222)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (3, N'eee', N'fff', 3333)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (4, N'hhh', N'ggg', 4444)
SET IDENTITY_INSERT [dbo].[TableA] OFF
SET IDENTITY_INSERT [dbo].[TableB] ON
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (1, N'clean house')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (2, N'wash dishes')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (3, N'go shopping')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (4, N'cooking')
SET IDENTITY_INSERT [dbo].[TableB] OFF
SET IDENTITY_INSERT [dbo].[TableC] ON
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (1, 1, 2)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (2, 1, 1)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (3, 2, 3)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (5, 3, 1)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (7, 3, 4)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (8, 4, 1)
SET IDENTITY_INSERT [dbo].[TableC] OFF
ALTER TABLE [dbo].[TableC] WITH CHECK ADD CONSTRAINT [FK_TableC_TableA] FOREIGN KEY([A's Id])
REFERENCES [dbo].[TableA] ([Id])
GO
ALTER TABLE [dbo].[TableC] CHECK CONSTRAINT [FK_TableC_TableA]
GO
ALTER TABLE [dbo].[TableC] WITH CHECK ADD CONSTRAINT [FK_TableC_TableB] FOREIGN KEY([B's Id])
REFERENCES [dbo].[TableB] ([Id])
GO
ALTER TABLE [dbo].[TableC] CHECK CONSTRAINT [FK_TableC_TableB]
GO
-
Thanks for the ddl and sample data. However, what you posted is not like what you originally posted. More importantly there is not enough sample data. If I understand what you are doing correctly there are not more than 1 activity for any row here. Can you post some additional data to represent the problem. Additionally if you could post what the desired output is based on your sample data that would be awesome.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I have edited my last post to put the updated script. My desire is to have result like D the which is the last table on my first post. Thanks a lot
-
Using the examples from the article I posted you should be able to come up with something like this.
with SortedValues as
(
select a.*
, b.Task
from TableC c
join TableA a on a.Id = c.AId
join TableB b on b.Id = c.BId
)
select Id
, FirstName + ' ' + LastName as FullName
, PhoneNumber
, STUFF((Select ', ' + sv2.Task
from SortedValues sv2
where sv2.Id = sv.Id
order by sv2.Task
for XML PATH('')), 1, 1, ' ')
from SortedValues sv
group by Id
, FirstName + ' ' + LastName
, PhoneNumber
Make sure you read that article and understand what is going on with this query. Feel free to ask questions if you are uncertain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
AAAAAAA It works, Thanks a lotttt!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply