SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenating rows from aliased tables on MSSQL 2005 server


Concatenating rows from aliased tables on MSSQL 2005 server

Author
Message
domingo.sqlservercentral
domingo.sqlservercentral
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 35
It is my sample data:



-- creating table APERSON
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aperson](
[lid] [int] NOT NULL,
[szname] [char](50) NOT NULL,
CONSTRAINT [pk_aperson] PRIMARY KEY NONCLUSTERED
(
[lid] 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

USE concat_test
INSERT INTO [dbo].[aperson]
(lid, szname)
SELECT '1','Paul McCartney' UNION ALL
SELECT '2','Bob Dylan' UNION ALL
SELECT '3','Jimmy Bralower' UNION ALL
SELECT '4','Jimmy Cliff' UNION ALL
SELECT '5','Glenn Miller' UNION ALL
SELECT '6','Neal Conway' UNION ALL
SELECT '7','Louis Armstrong' UNION ALL
SELECT '8','Dave Stewart' UNION ALL
SELECT '19','Genesis' UNION ALL
SELECT '20','Mike Rutherford' UNION ALL
SELECT '9','Martin Gore' UNION ALL
SELECT '10','Diana Krall' UNION ALL
SELECT '11','Donovan' UNION ALL
SELECT '12','Edith Piaf' UNION ALL
SELECT '13','Freddie Mercury' UNION ALL
SELECT '14','Pet Shop Boys' UNION ALL
SELECT '15','Queen' UNION ALL
SELECT '16','The Beatles' UNION ALL
SELECT '17','Eurythmics' UNION ALL
SELECT '18','Annie Lennox'
GO





-- creating table AROLLE
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[arolle](
[lid] [int] NOT NULL,
[szname] [char](40) NOT NULL,
CONSTRAINT [pk_arolle] PRIMARY KEY NONCLUSTERED
(
[lid] 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

USE concat_test
INSERT INTO [dbo].[arolle]
(lid, szname)
SELECT '1','Performer' UNION ALL
SELECT '2','Author' UNION ALL
SELECT '3','Composer' UNION ALL
SELECT '4','Arrangeur'
GO






-- creating table JINGLE
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[jingle](
[lid] [int] NOT NULL,
[sztitle] [char](50) NULL,
[lpublisherid] [int] NULL,
[szshortinfo] [char](80) NULL,
CONSTRAINT [pk_jingle] PRIMARY KEY NONCLUSTERED
(
[lid] 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

USE concat_test
INSERT INTO [dbo].[jingle]
(lid, sztitle, lpublisherid,szshortinfo)
SELECT '1','StationID','1','IDshortinfo' UNION ALL
SELECT '2','Traffic','2','TRAFFICshortinfo' UNION ALL
SELECT '3','Carpet','3','CARPETshortinfo'
GO







-- creating table JINGLEPERSONROLLE

USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[jinglepersonrolle](
[ljingleid] [int] NOT NULL,
[larolleid] [int] NOT NULL,
[lapersonid] [int] NOT NULL,
CONSTRAINT [pk_xjpr] PRIMARY KEY NONCLUSTERED
(
[ljingleid] ASC,
[larolleid] ASC,
[lapersonid] 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

USE concat_test
INSERT INTO [dbo].[jinglepersonrolle]
(ljingleid, larolleid, lapersonid)
SELECT '1','1','17' UNION ALL
SELECT '1','2','18' UNION ALL
SELECT '1','3','8' UNION ALL
SELECT '1','4','8' UNION ALL
SELECT '2','1','5' UNION ALL
SELECT '2','1','14' UNION ALL
SELECT '2','2','2' UNION ALL
SELECT '2','2','7' UNION ALL
SELECT '2','3','1' UNION ALL
SELECT '2','3','2' UNION ALL
SELECT '3','1','15' UNION ALL
SELECT '3','1','16' UNION ALL
SELECT '3','1','19' UNION ALL
SELECT '3','2','1' UNION ALL
SELECT '3','2','2' UNION ALL
SELECT '3','2','20' UNION ALL
SELECT '3','3','1' UNION ALL
SELECT '3','3','2' UNION ALL
SELECT '3','3','20' UNION ALL
SELECT '3','4','13'
GO







-- creating table PROGRAMMITEM
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[programmitem](
[lid] [int] NOT NULL,
CONSTRAINT [pk_programmitem] PRIMARY KEY NONCLUSTERED
(
[lid] 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

USE concat_test
INSERT INTO [dbo].[programmitem]
(lid)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3'
GO






-- creating table PUBLISHER
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[publisher](
[lid] [int] NOT NULL,
[szname] [varchar](120) NULL,
CONSTRAINT [pk_publisher] PRIMARY KEY NONCLUSTERED
(
[lid] 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

USE concat_test
INSERT INTO [dbo].[publisher]
(lid, szname)
SELECT '1','EMI' UNION ALL
SELECT '2','Warner' UNION ALL
SELECT '3','Universal'
GO






-- creating table S_ELEMENT
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[s_element](
[planelemid] [int] NOT NULL,
[planheaderid] [int] NULL,
[lprogrammitemtypid] [int] NULL,
[lprogrammitemid] [int] NULL,
[sendeplatz] [datetime] NULL,
CONSTRAINT [pk_s_element] PRIMARY KEY NONCLUSTERED
(
[planelemid] 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

USE concat_test
INSERT INTO [dbo].[s_element]
(planelemid, planheaderid, lprogrammitemtypid, lprogrammitemid, sendeplatz)
SELECT '1','1','30','1','2014-03-25 12:31:00' UNION ALL
SELECT '2','1','30','2','2014-03-25 13:33:00' UNION ALL
SELECT '3','1','30','3','2014-03-25 14:38:00' UNION ALL
SELECT '4','1','30','1','2014-03-25 15:41:00' UNION ALL
SELECT '5','1','30','2','2014-03-25 16:45:00' UNION ALL
SELECT '6','1','30','3','2014-03-25 17:51:00'
GO





-- creating table S_PLANHDH
USE [concat_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[s_planhdh](
[planheaderid] [int] NOT NULL,
[filetype] [char](2) NULL,
CONSTRAINT [pk_s_planhdh] PRIMARY KEY NONCLUSTERED
(
[planheaderid] 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

USE concat_test
INSERT INTO [dbo].[s_planhdh]
(planheaderid, filetype)
SELECT '1','I'
GO


domingo.sqlservercentral
domingo.sqlservercentral
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 35
... and current result is:

2014-03-25 12:31:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan Paul McCartney Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan Paul McCartney Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan Bob Dylan Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan Bob Dylan Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Louis Armstrong Paul McCartney Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Louis Armstrong Paul McCartney Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Louis Armstrong Bob Dylan Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 13:33:00.000 Traffic Louis Armstrong Bob Dylan Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Bob Dylan Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 14:38:00.000 Carpet Mike Rutherford Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 15:41:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan Paul McCartney Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan Paul McCartney Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan Bob Dylan Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan Bob Dylan Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Louis Armstrong Paul McCartney Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Louis Armstrong Paul McCartney Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Louis Armstrong Bob Dylan Glenn Miller Warner 2 TRAFFICshortinfo
2014-03-25 16:45:00.000 Traffic Louis Armstrong Bob Dylan Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Paul McCartney Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Bob Dylan Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Paul McCartney Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Paul McCartney The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Paul McCartney Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Bob Dylan Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Bob Dylan The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Bob Dylan Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Mike Rutherford Queen Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Mike Rutherford The Beatles Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 17:51:00.000 Carpet Mike Rutherford Mike Rutherford Genesis Freddie Mercury Universal 3 CARPETshortinfo
domingo.sqlservercentral
domingo.sqlservercentral
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 35
... and I need this rows concatenated as below:

2014-03-25 12:31:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan, Louis Armstrong Paul McCartney, Bob Dylan Glenn Miller, Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney, Bob Dylan, Mike Rutherford Paul McCartney, Bob Dylan, Mike Rutherford Queen, The Beatles, Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 15:41:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan, Louis Armstrong Paul McCartney, Bob Dylan Glenn Miller, Pet Shop Boys Warner 2 TRAFFICshortinfo
domingo.sqlservercentral
domingo.sqlservercentral
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 35
... and? Please...
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26098 Visits: 17536
domingo.sqlservercentral (4/4/2014)
... and? Please...


Well you gave us a bunch of data and said "here is my results". Results of what? Presumably a query.

The problem is that you have the same column name in a number of these tables and there is nothing indicate the relation between these tables. How about if you post the query that you used and explain what you want as output?

This is a two way street. When you provide details about what you want we can help you figure out a way to get them. Otherwise we are just guessing.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5460 Visits: 35404
Sean Lange (4/4/2014)
domingo.sqlservercentral (4/4/2014)
... and? Please...


Well you gave us a bunch of data and said "here is my results". Results of what? Presumably a query.

The problem is that you have the same column name in a number of these tables and there is nothing indicate the relation between these tables. How about if you post the query that you used and explain what you want as output?

This is a two way street. When you provide details about what you want we can help you figure out a way to get them. Otherwise we are just guessing.


Sean...I think the query is in the original post,,



SELECT
s_element.sendeplatz,
RTRIM (ISNULL(jingle.SzTitle, '')),
ISNULL (auths.szname, ''),
ISNULL (comps.szname, ''),
ISNULL (performs.szname, ''),
ISNULL (arrangs.szname, ''),
ISNULL (publisher.szname, ''),
RTRIM (ISNULL(jingle.lid, '')),
RTRIM (ISNULL(jingle.szshortinfo, ''))

FROM s_planhdh
LEFT OUTER JOIN s_element ON s_element.planheaderid = s_planhdh.planheaderid
LEFT OUTER JOIN programmitem ON programmitem.lid = s_element.lprogrammitemid
LEFT OUTER JOIN jingle ON jingle.lid = s_element.lprogrammitemid
LEFT OUTER JOIN publisher ON publisher.lid = jingle.lpublisherid
LEFT OUTER JOIN jinglepersonrolle AS authors ON authors.ljingleid = jingle.lid AND authors.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Author')
LEFT OUTER JOIN jinglepersonrolle AS composers ON composers.ljingleid = jingle.lid AND composers.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Composer')
LEFT OUTER JOIN jinglepersonrolle AS arrangeurs ON arrangeurs.ljingleid = jingle.lid AND arrangeurs.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Arrangeur')
LEFT OUTER JOIN jinglepersonrolle AS performers ON performers.ljingleid = jingle.lid AND performers.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Performer')
LEFT OUTER JOIN aperson AS comps ON comps.lid = composers.lapersonid
LEFT OUTER JOIN aperson AS auths ON auths.lid = authors.lapersonid
LEFT OUTER JOIN aperson AS arrangs ON arrangs.lid = arrangeurs.lapersonid
LEFT OUTER JOIN aperson AS performs ON performs.lid = performers.lapersonid

WHERE
lprogrammitemtypid=30
AND filetype='I'

ORDER BY s_element.sendeplatz




________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26098 Visits: 17536
Ah so it is. My bad. So...

Can you explain the output you posted? It is all text on screen so there is no separation of columns. Is this a comma separated list for each SzTitle and date maybe???

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5460 Visits: 35404
domingo.sqlservercentral (3/26/2014)
... and I need this rows concatenated as below:

2014-03-25 12:31:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 13:33:00.000 Traffic Bob Dylan, Louis Armstrong Paul McCartney, Bob Dylan Glenn Miller, Pet Shop Boys Warner 2 TRAFFICshortinfo
2014-03-25 14:38:00.000 Carpet Paul McCartney, Bob Dylan, Mike Rutherford Paul McCartney, Bob Dylan, Mike Rutherford Queen, The Beatles, Genesis Freddie Mercury Universal 3 CARPETshortinfo
2014-03-25 15:41:00.000 StationID Annie Lennox Dave Stewart Eurythmics Dave Stewart EMI 1 IDshortinfo
2014-03-25 16:45:00.000 Traffic Bob Dylan, Louis Armstrong Paul McCartney, Bob Dylan Glenn Miller, Pet Shop Boys Warner 2 TRAFFICshortinfo


am interested in what you intend to do with your output above...I don't see any determining column to distinguish authors from composers from performers etc....???

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5460 Visits: 35404
this may help you on your way....



USE [tempdb]
GO

-- reconfigured your original query...and removed the subeselects and aliases
-- hopefully you can follow structure

SELECT s_element.sendeplatz,
jingle.sztitle,
arolle.szname AS ptype,
aperson.szname AS pname,
publisher.szname AS pub,
jingle.lid AS JID,
jingle.szshortinfo
INTO #tmp
FROM s_element
INNER JOIN s_planhdh ON s_element.planheaderid = s_planhdh.planheaderid
INNER JOIN jingle ON s_element.lprogrammitemid = jingle.lid
INNER JOIN jinglepersonrolle ON jingle.lid = jinglepersonrolle.ljingleid
INNER JOIN arolle ON jinglepersonrolle.larolleid = arolle.lid
INNER JOIN publisher ON jingle.lid = publisher.lid
INNER JOIN aperson ON jinglepersonrolle.lapersonid = aperson.lid
WHERE (s_planhdh.filetype = 'I') AND (s_element.lprogrammitemtypid = 30)

SELECT * FROM #tmp

--is this what you are looking for below ??

SELECT
sendeplatz,
sztitle,
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Author'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Composer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Performer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
ISNULL(STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Arrangeur'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' '), '') ,
pub ,
JID ,
szshortinfo
FROM #tmp p1
GROUP BY sendeplatz,sztitle,pub,JID,szshortinfo

DROP TABLE [dbo].[#tmp]
GO





________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

domingo.sqlservercentral
domingo.sqlservercentral
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 35
Sean Lange (4/4/2014)
Ah so it is. My bad. So...
Can you explain the output you posted? It is all text on screen so there is no separation of columns. Is this a comma separated list for each SzTitle and date maybe???


I'm sorry. In fact, I posted that outputs without visible separators, they was removed on this page (result was tab separated). But, in the needed result I wrote, needed separation is visible by the space and the comma locations (comma means concatenated data, and space means separation - next column).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search