• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13
  • Last Modified:

SQL consolidate results to a single cell

is there a way to consolidate results into a single cell, i want only one line per 'SignalID' unfortunatly the table SigLog as described below, has many entries  per 'SignalID' i would like to consolidate each entry from Siglog.faction that contains a 7 or 10 digit phone number as one cell in my results, instead of a separate row for each entry.
i don't know how to approach this.
SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) AS [Alarm Date], 
       ISNULL(Subscriber.AcctLineCode, 'XX') AS LC, 
       Subscriber.AcctNum, 
       Subscriber.Name, 
       Codes.Description, 
       SignalHistory.AlarmZones AS [Zone #], 
       ISNULL(Zones.Description, '') AS [Zone Description], 
       Subscriber.Address, 
       Subscriber.City, 
       Subscriber.State, 
       SignalHistory.RawData,
       Subscriber.MainPhone,
       MISC.Disposition,
       siglog.fAction
FROM [History0218].[dbo].[SignalHistory] SignalHistory
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
LEFT JOIN [History0218].[dbo].[SignalLog] siglog          ON SignalHistory.SignalID = siglog.SignalID 
LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones           ON SignalHistory.AccountID = Zones.AccountID 
                                                          AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
                                                          
LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes      ON SignalHistory.AlarmCode = Codes.Code
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Status] Stat     ON SignalHistory.AccountID = Stat.AccountID
                                                          AND SignalHistory.AlarmDate BETWEEN Stat.TestStart AND Stat.TestEnd
WHERE SignalHistory.AlarmDate >= DATEADD(DAY, -7, GETUTCDATE()) 
AND Subscriber.AcctLineCode IN ('ds', 'dsa')
AND SignalHistory.DispositionID IN ('2', '23')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

0
csePixelated
Asked:
csePixelated
  • 4
  • 2
2 Solutions
 
Mark WillsTopic AdvisorCommented:
What a pity you arent already on SQL Server 2017 - it introduces a new function named STRING_AGG()
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

But, I digress :)

We can take advantage of "for XML" to get a comma delimited string (and not an XML string) of the other columns in the row.

Is the phone number obvious / clearly identifiable in faction ? Ideally a seperate column ?

If you are doing LEFT JOIN [History0218].[dbo].[SignalLog] siglog ON SignalHistory.SignalID = siglog.SignalID  is that already showing every phone number but as separate rows ?  In which case, we will want to replace all the separate rows with the delimited string of phone numbers (so, replacing siglog.fAction in the select) - right ?

Challenge is, you are already selecting ... from  [History0218].[dbo].[SignalHistory] SignalHistory
and then joining to the signallog table  LEFT JOIN [History0218].[dbo].[SignalLog] siglog ON SignalHistory.SignalID = siglog.SignalID

Hopefully SignalHistory does not contain multi instance of signalid for those (new) faction rows you need (rather, signallog has multi-rows per signalhistory)...

Any sample data of what we might expect from siglog.fAction ?

Cheers,
Mark
0
 
Mark WillsTopic AdvisorCommented:
Hmmm, think my post is a bit confusing above. So a quick little example. Here I am creating two temp tables representing [History0218].[dbo].[SignalHistory] and   [History0218].[dbo].[SignalLog]
create table #SignalHistory(SignalID int)
insert #SignalHistory values
(1),
(2),
(3),
(4)

create table #signallog (SignalID int, faction varchar(20))
insert #signallog values 
(1,'111-222-333'), 
(1,'111-333-444'), 
(1,'111-444-555'),
(2,'222-222-333'), 
(2,'222-333-444'), 
(3,'333-444-555')

Open in new window

Now, using those temp tables, we can demonstrate how to get those Cell Phones using a cross apply instead of a left outer join
select SignalHistory.signalid,
       Siglog.faction as CellPhones
FROM #SignalHistory SignalHistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from #SignalLog siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'   -- this step is important - must be able to identify Cell Phones
             for XML path('')),1,1,'')) siglog(faction)

Open in new window

You can try the above select / cross apply query for yourself on your real tables by substituting #SignalHistory and #SignalLog with [History0218].[dbo].[SignalHistory] and   [History0218].[dbo].[SignalLog] respectively. It will also be important to be accurately identify phone numbers in faction so might need to do extra work there.
0
 
csePixelatedAuthor Commented:
Unfortunately the phone number is not the only thing in the string,  you are correct [SignalID] is a unique key on the [SignalHistory] and has multiple instances on [SignalLog].
Can i use LTRIM to adjust for the extra characters yet still have them included in the final results if not ill live with just the numbers.
I have no way to test it right now, ill try the above in 12 hours.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Mark WillsTopic AdvisorCommented:
I am expecting ltrim() and a lot more :)

lets try it as is, see what we get and decide what is needed from a first "play"
 
Once you have run using the test (temp) tables, then try
select SignalHistory.signalid,
       Siglog.faction as CellPhones
FROM [History0218].[dbo].[SignalHistory] SignalHistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                           -- this step is important - must be able to identify Cell Phones must change accordingly
             for XML path('')),1,1,'')) siglog(faction)

Open in new window

0
 
csePixelatedAuthor Commented:
Works beautifully, thank you Mark!
SELECT DATEADD(HOUR, -6, SignalHistory.AlarmDate) AS [Alarm Date], 
       ISNULL(Subscriber.AcctLineCode, 'XX') AS LC, 
       Subscriber.AcctNum, 
       Subscriber.Name, 
       Codes.Description, 
       SignalHistory.AlarmZones AS [Zone #], 
       ISNULL(Zones.Description, '') AS [Zone Description], 
       Subscriber.Address, 
       Subscriber.City, 
       Subscriber.State, 
       SignalHistory.RawData,
       Subscriber.MainPhone,
       MISC.Disposition,
       Siglog.faction as CellPhones
FROM [History0218].[dbo].[SignalHistory] SignalHistory
cross apply (select stuff((select ','+isnull(faction,'') 
             from [History0218].[dbo].[SignalLog] siglog 
             where siglog.SignalID = SignalHistory.SignalID 
             and siglog.faction like '%-%-%'                                           -- this step is important - must be able to identify Cell Phones must change accordingly
             for XML path('')),1,1,'')) siglog(faction)
LEFT JOIN [MISCELLANEOUS].[dbo].[Dispositions] MISC       ON SignalHistory.DispositionID = MISC.DispositionID
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones           ON SignalHistory.AccountID = Zones.AccountID 
                                                          AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
                                                          
LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes      ON SignalHistory.AlarmCode = Codes.Code
LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Status] Stat     ON SignalHistory.AccountID = Stat.AccountID
                                                          AND SignalHistory.AlarmDate BETWEEN Stat.TestStart AND Stat.TestEnd
WHERE SignalHistory.AlarmDate >= DATEADD(DAY, -22, GETUTCDATE()) 
AND Subscriber.AcctLineCode IN ('ds', 'dsa')
AND SignalHistory.DispositionID IN ('2', '23')
ORDER BY SignalHistory.AlarmDate DESC

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Well, that was a pleasant surprise.... Was expecting ltrim() and maybe an occasional charindex() to help identify and isolate just the phone numbers :)

Glad it works for you - and very happy to have been able to help.

Cheers,
Mark
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now