Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Concatenating rows from aliased tables on MSSQL 2005 server Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 4:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
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

Post #1554854
Posted Wednesday, March 26, 2014 4:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
... 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
Post #1554857
Posted Wednesday, March 26, 2014 4:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
... 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
Post #1554873
Posted Friday, April 4, 2014 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
... and? Please...
Post #1558572
Posted Friday, April 4, 2014 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
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 Moden's 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)
Post #1558574
Posted Friday, April 4, 2014 12:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 1,899, Visits: 18,901
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 !
__________________________________________________________________
Post #1558616
Posted Friday, April 4, 2014 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
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 Moden's 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)
Post #1558621
Posted Friday, April 4, 2014 2:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 1,899, Visits: 18,901
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 !
__________________________________________________________________
Post #1558657
Posted Saturday, April 5, 2014 6:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 1,899, Visits: 18,901
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 !
__________________________________________________________________
Post #1558722
Posted Wednesday, April 9, 2014 3:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
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).
Post #1559852
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse