SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert SQL results into single row of data


Convert SQL results into single row of data

Author
Message
paul.stevens 24552
paul.stevens 24552
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 17
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156842 Visits: 23036
Quick suggestion
Cool

DECLARE @test TABLE (
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
(
SELECT
CONCAT(T.LocationName,CHAR(32),T.Activecases,CHAR(32),T.LastSeen,CHAR(32),T.Refresh_date) + CHAR(32)
FROM @test 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 Wink
paul.stevens 24552
paul.stevens 24552
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 17
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
DesNorton
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18123 Visits: 7618
Take a look at the PIVOT operator


How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160703 Visits: 22698
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
paul.stevens 24552
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 17
Hi All, Thanks for the advice. Got it sorted using a PIVOT and cross apply combination.

Regards
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)SSC Guru (860K reputation)

Group: General Forum Members
Points: 860080 Visits: 47118
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
paul.stevens 24552
paul.stevens 24552
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 17
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
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160703 Visits: 22698
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search