Flattening Data: SQL Server 2008

Posted on 2011-10-31
Last Modified: 2012-05-12
I am trying to flatten a table -- I am almost there, but I can't get rid of the separator I dubbed in when I don't need it. In a nutshell, here is what this is about. I have a Site table and I have a Facility table. I can have multiple facilities for a Site, so I want to just add the facilities found like so: 200;400

So instead of:
SiteID      FacilityNumber
200          200
200          400

I want to create:
SiteID      FacilityNumber
200         200;400

What I am getting back from my code however is this:
SiteID      FacilityNumber
200         200;400;

Take a look at what I do have--and please advise on how I can get rid of that last ';'.
     (SELECT (replace(f.Number,'','') + ';')
       FROM Facility f, [Site] s2
        WHERE s2.SiteID = f.SiteID
          and s2.SiteID = s1.SiteID
         ORDER by f.Number
          FOR XML PATH('')) AS [Number]
     FROM [Site] s1

Open in new window

Question by:ditallop
    LVL 26

    Accepted Solution

    try this

    SELECT s1.SiteID,
         STUFF((SELECT ';'+f.Number
           FROM Facility f, [Site] s2
            WHERE s2.SiteID = f.SiteID
              and s2.SiteID = s1.SiteID
             ORDER by f.Number
              FOR XML PATH('')),1,1,'') AS [Number]
         FROM [Site] s1
       GROUP BY SiteID

    Author Closing Comment

    Brilliantly done!;-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now