Concatenating rows from aliased tables on MSSQL 2005 server

  • 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.

  • 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]

  • 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.

  • Hi, could anybody help me? Please.

    Or please tell me, where (on which forum) can I find an aid.

  • 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

  • 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

  • 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.

  • 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/

  • 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.

  • Ok, I'll try to prepare simple data as you mentioned and put it here.

  • 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

  • ... 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

  • ... 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

  • ... and? Please...

  • 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