March 4, 2014 at 9:59 am
Hi Everyone, I'm newbie here and in TSQL too, so please be lenient and patient for me 😉
I have some trouble with row concatenation running MSSQL server 2005.
I wrote this query below and it works well when for some row from s_element, all of four tables (aliased here as: authors, composers, performers, arrangeurs) have the one only rows matching (or have none).
But when for some row from s_element are more matching persons in these 4 tables, my query multiply results for this one row from s_element (with this persons in other result rows).
My intention is, that query returns not multiplicated rows from s_element with concatenated matched rows from these four tables (and separated with some special character).
The code is:
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
My knownedge of TSQL is not enough, so please help me.
Kind regards, Rob.
March 4, 2014 at 2:18 pm
domingo.sqlservercentral (3/4/2014)
My intention is, that query returns not multiplicated rows from s_element with concatenated matched rows from these four tables (and separated with some special character).
OK, then what do you want it to do when there are multiple matches for one of the rows?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2014 at 4:14 pm
RBarryYoung (3/4/2014)
domingo.sqlservercentral (3/4/2014)
My intention is, that query returns not multiplicated rows from s_element with concatenated matched rows from these four tables (and separated with some special character).OK, then what do you want it to do when there are multiple matches for one of the rows?
Oh, I'm so sorry for my mistake in that post 😉
My intention was, that query returns not multiplicated rows from s_element, BUT with concatenated matched rows from these four tables (separated with some special character). And number of returned rows equal to the situation without many matched persons from any of these four person aliased tables.
March 17, 2014 at 8:50 am
Hi, could anybody help me? Please.
Or please tell me, where (on which forum) can I find an aid.
March 17, 2014 at 9:22 am
sample data would help us help you....do you know how to provide this?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 18, 2014 at 6:18 am
Do you mean like this:
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, '')) AS CatColumn
March 18, 2014 at 6:43 am
J Livingston SQL (3/17/2014)
sample data would help us help you....do you know how to provide this?
No, I don't know. Do you mean about upload of database backup file? Or that I write you a script for generating sample base properly for the query I asked?
drew.king1 (3/18/2014)
Do you mean like this: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, '')) AS CatColumn
No, I don't. This concatinate *constant number* of different columns, and no multiple (indeterminate number) of the same parameter rows.
March 18, 2014 at 7:53 am
domingo.sqlservercentral (3/18/2014)
J Livingston SQL (3/17/2014)
sample data would help us help you....do you know how to provide this?No, I don't know. Do you mean about upload of database backup file? Or that I write you a script for generating sample base properly for the query I asked?
What he was eluding to is that in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article 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/
March 19, 2014 at 7:49 am
domingo.sqlservercentral (3/18/2014)
J Livingston SQL (3/17/2014)
sample data would help us help you....do you know how to provide this?
No, I don't know. Do you mean about upload of database backup file? Or that I write you a script for generating sample base properly for the query I asked?
This just means that in order to help you it is best to provide some code and data that can help them recreate the problem and craft an answer.
You might do something like this:
<YOUR PROBLEM DEFINITION>
Blah blah
<SAMPLE DDL AND DATA>
CREATE TABLE #tmp(myfirstcol SMALLINT, mysecondcol SMALLINT);
INSERT #tmp(myfirstcol,
mysecondcol)
SELECT 1,2
<CURRENT RESULTS>
SELECT * FROM #tmp;
myfirstcol mysecondcol
12
<EXPECTED RESULTS>
mycol
1
2
Obviously, in your case you will need to provide ddl and data that accurately reflects your problem.
March 19, 2014 at 9:45 am
Ok, I'll try to prepare simple data as you mentioned and put it here.
March 26, 2014 at 4:23 am
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
March 26, 2014 at 4:26 am
... and current result is:
2014-03-25 12:31:00.000StationIDAnnie Lennox Dave Stewart Eurythmics Dave Stewart EMI1IDshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan Paul McCartney Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan Paul McCartney Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan Bob Dylan Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan Bob Dylan Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficLouis Armstrong Paul McCartney Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficLouis Armstrong Paul McCartney Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficLouis Armstrong Bob Dylan Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 13:33:00.000TrafficLouis Armstrong Bob Dylan Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetBob Dylan Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 14:38:00.000CarpetMike Rutherford Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 15:41:00.000StationIDAnnie Lennox Dave Stewart Eurythmics Dave Stewart EMI1IDshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan Paul McCartney Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan Paul McCartney Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan Bob Dylan Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan Bob Dylan Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficLouis Armstrong Paul McCartney Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficLouis Armstrong Paul McCartney Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficLouis Armstrong Bob Dylan Glenn Miller Warner2TRAFFICshortinfo
2014-03-25 16:45:00.000TrafficLouis Armstrong Bob Dylan Pet Shop Boys Warner2TRAFFICshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetPaul McCartney Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetBob Dylan Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Paul McCartney Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Paul McCartney The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Paul McCartney Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Bob Dylan Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Bob Dylan The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Bob Dylan Genesis Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Mike Rutherford Queen Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Mike Rutherford The Beatles Freddie Mercury Universal3CARPETshortinfo
2014-03-25 17:51:00.000CarpetMike Rutherford Mike Rutherford Genesis Freddie Mercury Universal3CARPETshortinfo
March 26, 2014 at 4:42 am
... and I need this rows concatenated as below:
2014-03-25 12:31:00.000StationIDAnnie LennoxDave StewartEurythmicsDave StewartEMI1IDshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan, Louis ArmstrongPaul McCartney, Bob DylanGlenn Miller, Pet Shop BoysWarner2TRAFFICshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney, Bob Dylan, Mike RutherfordPaul McCartney, Bob Dylan, Mike RutherfordQueen, The Beatles, GenesisFreddie MercuryUniversal3CARPETshortinfo
2014-03-25 15:41:00.000StationIDAnnie LennoxDave StewartEurythmicsDave StewartEMI1IDshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan, Louis ArmstrongPaul McCartney, Bob DylanGlenn Miller, Pet Shop BoysWarner2TRAFFICshortinfo
April 4, 2014 at 9:28 am
... and? Please...
April 4, 2014 at 9:42 am
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 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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply