Convert SQL results into single row of data

Question

Post reply

Convert SQL results into single row of data

paul.stevens 24552

SSC Veteran

Points: 249
More actions
January 2, 2018 at 3:15 am

#342194

Hi
I have been asked to convert the results from a select statement into a single row of data. There will never be more than 3 - 5 rows in the initial data set.
Test Data
CREATE TABLE test (
LocationName varchar(255),
Activecases int,
LastSeen DATETIME,
Refresh_date DATETIME,
);
INSERT INTO test (LocationName, Activecases, LastSeen, Refresh_date )
VALUES ('Alston', 3 , '2017-09-09 20:04:01.570', '2017-09-09 20:04:01.570') ,
('Keswick', 2 , '2018-01-02 08:33:18.320', '2017-09-09 20:04:01.570') ,
('Penrith', 5 , '2018-01-02 09:06:43.850', '2017-09-09 20:04:01.570')
SELECT
* FROM test
From the above data i wish to view it in a single row as represented below if possible.
LocationName_1 Activecases_1 LastSeen_1 Refresh_date_1 LocationName_2 Activecases_2 LastSeen_2 Refresh_date_2 LocationName_3 Activecases_3 LastSeen_3 Refresh_date_3
Alston 3 2017-09-09 20:04:01.570 2017-09-09 20:04:01.570 Keswick 2 2018-01-02 08:33:18.320 2017-09-09 20:04:01.570 Penrith 5 2018-01-02 09:06:43.850 2017-09-09 20:04:01.570
Any help or advice would be appreciated

Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick suggestion
😎

DECLARE @test-2 TABLE ( LocationName varchar(255), Activecases int, LastSeen DATETIME, Refresh_date DATETIME );

INSERT INTO @test-2 (LocationName, Activecases, LastSeen, Refresh_date ) VALUES ('Alston', 3 , '2017-09-09 20:04:01.570', '2017-09-09 20:04:01.570') , ('Keswick', 2 , '2018-01-02 08:33:18.320', '2017-09-09 20:04:01.570') , ('Penrith', 5 , '2018-01-02 09:06:43.850', '2017-09-09 20:04:01.570')

SELECT ( SELECT CONCAT(T.LocationName,CHAR(32),T.Activecases,CHAR(32),T.LastSeen,CHAR(32),T.Refresh_date) + CHAR(32) FROM @test-2 T FOR XML PATH(''), TYPE).value('(./text())[1]','NVARCHAR(MAX)') AS ALL_IN_ONE

Output
Alston 3 Sep 9 2017 8:04PM Sep 9 2017 8:04PM Keswick 2 Jan 2 2018 8:33AM Sep 9 2017 8:04PM Penrith 5 Jan 2 2018 9:06AM Sep 9 2017 8:04PM

You can use the CONVERT function to get the dates into the desired format 😉

paul.stevens 24552 SSC Veteran Points: 249 More actions · Answer 2

Hi, thanks for the reply but i need to get them into separate columns rather than a single sting ????

Apologies if my example gave the wrong impression.

Regards

DesNorton SSC-Insane Points: 24279 More actions · Answer 3

Take a look at the PIVOT operator

How to post data/code on a forum to get the best help.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Check the following articles:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

paul.stevens 24552 SSC Veteran Points: 249 More actions · Answer 5

Hi All, Thanks for the advice. Got it sorted using a PIVOT and cross apply combination.

Regards

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 6

paul.stevens 24552 - Tuesday, January 2, 2018 7:15 AM
Hi All, Thanks for the advice. Got it sorted using a PIVOT and cross apply combination.
Regards

Cool. Can you post the code you ended up with, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul.stevens 24552 SSC Veteran Points: 249 More actions · Answer 7

Basically has 3 versions of this for each of the desired columns ( ActiveCases, Last seen, & Refresh)
SELECT   *
FROM    (SELECT   LocationName, ActiveCases
         FROM    [FairWarning_Extract].[dbo].[vw_Adastra_Live]) AS s PIVOT (SUM(s.ActiveCases) FOR LocationName IN ([Alston MIU (PCAS)], [Clinics (PCAS)],
         [Kendal (PCAS)], [Maryport (PCAS)], [Penrith MIU (PCAS)])) AS PVT

then just cross applied them in a view with a few aliases on the column names as per below:

SELECT   ac.[Alston MIU (PCAS)] AS Alston_MIU_PCAS, ac.[Clinics (PCAS)] AS Clinics_PCAS, ac.[Kendal (PCAS)] AS Kendal_PCAS, ac.[Maryport (PCAS)] AS Maryport_PCAS,
         ac.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS, ls.[Alston MIU (PCAS)] AS Alston_MIU_PCAS_Last_Seen, ls.[Clinics (PCAS)] AS Clinics_PCAS_Last_Seen,
         ls.[Kendal (PCAS)] AS Kendal_PCAS_Last_Seen, ls.[Maryport (PCAS)] AS Maryport_PCAS_Last_Seen, ls.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS_Last_Seen,
         r.[Alston MIU (PCAS)] AS Alston_MIU_PCAS_Refresh, r.[Clinics (PCAS)] AS Clinics_PCAS_Refresh, r.[Kendal (PCAS)] AS Kendal_PCAS_Refresh,
         r.[Maryport (PCAS)] AS Maryport_PCAS_Refresh, r.[Penrith MIU (PCAS)] AS Penrith_MIU_PCAS_Refresh
FROM    dbo.vw_Adastra_Active_Cases AS ac CROSS JOIN
         dbo.vw_Adastra_Last_Seen AS ls CROSS JOIN
         dbo.vw_Adastra_Refresh_Date AS r

Sure there is a slicker way to do it but it works for what i need

Thanks Again.

P

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Yes, there's a simpler way to do it which is faster, too. It's on the articles I showed you and I'm guessing it can be at least 3 times faster in execution and won't need you to create additional objects. The articles also show how to make this dynamic.
SELECT MAX(CASE WHEN LocationName = 'Alston' THEN Activecases END) Alston_ActiveCases ,MAX(CASE WHEN LocationName = 'Keswick' THEN Activecases END) Keswick_ActiveCases ,MAX(CASE WHEN LocationName = 'Penrith' THEN Activecases END) Penrith_ActiveCases ,MAX(CASE WHEN LocationName = 'Alston' THEN LastSeen END) Alston_LastSeen ,MAX(CASE WHEN LocationName = 'Keswick' THEN LastSeen END) Keswick_LastSeen ,MAX(CASE WHEN LocationName = 'Penrith' THEN LastSeen END) Penrith_LastSeen ,MAX(CASE WHEN LocationName = 'Alston' THEN Refresh_date END) Alston_Refresh_date ,MAX(CASE WHEN LocationName = 'Keswick' THEN Refresh_date END) Keswick_Refresh_date ,MAX(CASE WHEN LocationName = 'Penrith' THEN Refresh_date END) Penrith_Refresh_date FROM test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2