Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!):

--------------------------------
shipments                       |
--------------------------------
idShipment | idRoute | idClient |
1          | 1       |     1    |
2          | 2       |     1    |
3          | 2       |     2    |
---------------------------------

-----------------------------------------
routes                                   |
-----------------------------------------
idRoute    | shortNameRoute |    name    |
1          | ACA            |  ACAPULCO  |
2          | MTY            |  MONTERREY |
-----------------------------------------

-------------------------
points                   |
-------------------------
idPoint    | PointName   |
1          | ACAPULCO    |
2          | ACAPULCO_2  |
3          | MONTERREY   |   
4          | MONTERREY_2 | 
--------------------------

-------------------------
clients                  |
-------------------------
idClient   | ClientName  |
1          | Mocosoft    |
2          | Mapple      |
--------------------------



 /*This table has the relationship between routes and points*/
--------------------------------
routePoints                     |
--------------------------------
idRoutePoint | idRoute | idPoint|
1            |   1     |   1    |
2            |   1     |   2    |
3            |   2     |   3    |
4            |   2     |   4    |
--------------------------------

How can I get the "point name(s)" for a route as a field in a result set? For example:


----------------------------------------------------------------
result                                                          |
----------------------------------------------------------------
idShipment | shortNameRoute | points                 | client   |
1          | ACA            | ACAPULCO,ACAPULCO_2    | Mocosoft |
2          | MTY            | MONTERREY,MONTERREY_2  | Mapple   |
-----------------------------------------------------------

To date I have been using .Net code to process the points by route. I imagine there is a more efficient way to do this with T-SQL.

Perhaps I need to "normalize" my database more efficiently?

I don't like performing a query request for every shipment, along with extra loops in my code, but until now, it's the only way and I'm kind of lost.

I expect the solution might involve something like PIVOT, SELECT nested or probably "WITH" Common Table Expressions, but, I'm not sure.

Note: I'm using SQLServer 2008 R2

Here is the script to create the DB with Data example (TEST_ROUTES)

USE [TEST_ROUTES]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[shipments]
(
    [idShipment] [int] IDENTITY(1,1) NOT NULL,
    [idRoute] [int] NOT NULL,
    [idClient] [int] NOT NULL,
    CONSTRAINT [PK_shipments] PRIMARY KEY CLUSTERED 
    (
        [idShipment] 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].[shipments] ON;

INSERT [dbo].[shipments] ([idShipment], [idRoute], [idClient]) VALUES (1, 1, 1);
INSERT [dbo].[shipments] ([idShipment], [idRoute], [idClient]) VALUES (2, 2, 1);
INSERT [dbo].[shipments] ([idShipment], [idRoute], [idClient]) VALUES (3, 2, 2);

SET IDENTITY_INSERT [dbo].[shipments] OFF;


SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[routes]
(
    [idRoute] [int] NOT NULL,
    [shortNameRoute] [nvarchar](50) NOT NULL,
    [routeName] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_routes] PRIMARY KEY CLUSTERED 
    (
        [idRoute] 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
INSERT [dbo].[routes] ([idRoute], [shortNameRoute], [routeName]) VALUES (1, N'ACA', N'ACAPULCO');
INSERT [dbo].[routes] ([idRoute], [shortNameRoute], [routeName]) VALUES (2, N'MTY', N'MONTERREY');

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[routePoints]
(
    [idRoutePoints] [int] NOT NULL,
    [idRoute] [int] NOT NULL,
    [idPoint] [int] NOT NULL,
    CONSTRAINT [PK_routePoints] PRIMARY KEY CLUSTERED 
    (
        [idRoutePoints] 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

INSERT [dbo].[routePoints] ([idRoutePoints], [idRoute], [idPoint]) VALUES (1, 1, 1);
INSERT [dbo].[routePoints] ([idRoutePoints], [idRoute], [idPoint]) VALUES (2, 1, 2);
INSERT [dbo].[routePoints] ([idRoutePoints], [idRoute], [idPoint]) VALUES (3, 2, 3);
INSERT [dbo].[routePoints] ([idRoutePoints], [idRoute], [idPoint]) VALUES (4, 2, 4);

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[points](
    [idPoint] [int] IDENTITY(1,1) NOT NULL,
    [PointName] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_points] PRIMARY KEY CLUSTERED 
    (
        [idPoint] 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].[points] ON;

INSERT [dbo].[points] ([idPoint], [PointName]) VALUES (1, N'ACAPULCO');
INSERT [dbo].[points] ([idPoint], [PointName]) VALUES (2, N'ACAPULCO_2');
INSERT [dbo].[points] ([idPoint], [PointName]) VALUES (3, N'MONTERREY');
INSERT [dbo].[points] ([idPoint], [PointName]) VALUES (4, N'MONTERREY_2');

SET IDENTITY_INSERT [dbo].[points] OFF;


SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[clients]
(
    [idClient] [int] IDENTITY(1,1) NOT NULL,
    [clientName] [nvarchar](50) NULL,
    CONSTRAINT [PK_clients] PRIMARY KEY CLUSTERED 
    (
        [idClient] 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].[clients] ON;

INSERT [dbo].[clients] ([idClient], [clientName]) VALUES (1, N'Mapple');
INSERT [dbo].[clients] ([idClient], [clientName]) VALUES (2, N'Mocosoft');

SET IDENTITY_INSERT [dbo].[clients] OFF;
share|improve this question
2  
Your question would be substantially improved (and easier to answer) if you replaced your text outline of the tables with DDL scripts to create the tables. Bonus votes for including scripts to insert sample data. –  Mark Storey-Smith Feb 2 '12 at 20:40
    
It's done Mark, thanks for the tip, and sorry. –  Allende Feb 3 '12 at 1:20

1 Answer 1

up vote 4 down vote accepted

There is a (crude) hack in SQL Server to do group_concats that should get what you need, below is a query for your example. I can't vouch for its performance so you will need to test/optimise as required.

select idshipment, shortnameroute,
(   
    select pointname +', ' 
    from points p
    inner join routepoints rp on rp.idpoint = p.idpoint
    where rp.idroute = r.idroute  for XML path ('')
), 
c.clientname
from shipments s
inner join [routes] r on r.idroute = s.idroute
inner join clients c on c.idclient = s.idclient
share|improve this answer
    
Thanks WT_W, your query did the trick, probably it has better performance that do an extra loop, and extra query for each shipment. Until I know, the max of shipments it's like 600 for each search, I guest it's a low charge, but well, I'm going to test your script on my DB of production, to see how it's going. Thanks and if you have andy article to help me understand the hack, I'll be really grateful –  Allende Feb 3 '12 at 1:28
    
Well now that's a sweet trick. I recently needed to do this without using a user-defined-function in order to list role membership in all databases on the server. I think this will do it... –  db2 Feb 3 '12 at 15:02
    
@Allende, I can't remember where I picked it up from originally but here are some articles: simple-talk.com/sql/t-sql-programming/… by Anith Sen and databases.aspfaq.com/general/… by Aaron Bertrand –  WT_W Feb 8 '12 at 1:35
    
Thanks @WT_W I'm gonna check ASAP to see If I can learn/understand them –  Allende Feb 8 '12 at 5:59
    
Here is the specific article source I think Aaron was referring to: simple-talk.com/sql/t-sql-programming/… –  Dan Sorensen May 23 '12 at 2:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.