SQL Query

Question

Post reply

SQL Query

mtz676

SSCrazy Eights

Points: 9214
More actions
November 4, 2016 at 12:17 am

#331763

col A colB Details
A1ValueA Details1
A2ValueA Details2
B1ValueB Details1
B2ValueB Details2
B3ValueB Details3
C1ValueC Details1
C2ValueC Details2
C3ValueC Details3
C4ValueC Details4
My select query should return
A ValueA Details1/ValueA Details2
B ValueB Details1/ValueB Details2/ValueB Details3
C ValueC Details1/ValueC Details2/ValueC Details3/ValueC Details4
How do get the above output.
Note:The query should work across all versions.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Its always nice to have a proper table structure for a sample data. something like this:

declare @Sample Table (GroupCode char(1), BinNumber int, ValueCode varchar(20), DetailCode varchar(20))

Insert into @Sample

Select 'A',1,'ValueA','Details1' union all

Select 'A',2,'ValueA','Details2' union all

Select 'B',1,'ValueB','Details1' union all

Select 'B',2,'ValueB','Details2' union all

Select 'B',3,'ValueB','Details3' union all

Select 'C',1,'ValueC','Details1' union all

Select 'C',2,'ValueC','Details2' union all

Select 'C',3,'ValueC','Details3' union all

Select 'C',4,'ValueC','Details4'

;

For Result

WITH CTE (GroupCode)

AS(

SELECT DISTINCT

m.GroupCode

FROM

@Sample m

)

SELECT

m.GroupCode, Adj.CSV

FROM

cte m

CROSS APPLY

(

SELECT

STUFF(

(SELECT

'/' + ValueCode +' '+ DetailCode

FROM

@Sample d

WHERE

m.GroupCode = d.GroupCode

ORDER BY

d.GroupCode, d.BinNumber

FOR XML PATH(''), type).value('(./text())[1]','varchar(max)')

,1,1,'') AS CSV

) Adj

;

Hope it helps.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Or this perhaps? The requirement is rather vague.

DECLARE @Sample TABLE (GroupCode char(1), BinNumber int, ValueCode varchar(20), DetailCode varchar(20))

INSERT INTO @Sample (GroupCode, BinNumber, ValueCode, DetailCode) VALUES

('A',1,'ValueA','Details1'), ('A',2,'ValueA','Details2'),

('B',1,'ValueB','Details1'), ('B',2,'ValueB','Details2'), ('B',3,'ValueB','Details3'),

('C',1,'ValueC','Details1'), ('C',2,'ValueC','Details2'), ('C',3,'ValueC','Details3'), ('C',4,'ValueC','Details4');

-- standard crosstab query

SELECT

GroupCode,

Newcolumn1 = MAX(CASE WHEN BinNumber = 1 THEN ValueCode + ' ' + DetailCode END),

Newcolumn2 = MAX(CASE WHEN BinNumber = 2 THEN ValueCode + ' ' + DetailCode END),

Newcolumn3 = MAX(CASE WHEN BinNumber = 3 THEN ValueCode + ' ' + DetailCode END),

Newcolumn4 = MAX(CASE WHEN BinNumber = 4 THEN ValueCode + ' ' + DetailCode END)

FROM @Sample

GROUP BY GroupCode

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 3

This is what I am trying to do.

select sj.name,sj.enabled,sjh.step_id,sjh.message from

sysjobhistory sjh inner join sysjobs sj

on sj.job_id=sjh.job_id

and sj.enabled='1'

In this query result.For every sj.name there will 1 or more sjh.step_id 's and for each sjh.step_id there will be 1 sjh.message.What I would like to display is as underneath columns

sj.name sjh.message

where sjh.message values may be 1 or 2 or 3 rows for as many sjh.step_id 's and all these 1 or 2 or 3 rows will be concatenated and displayed as 1 row for each sj.name

Hope my explanation is clear.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Quick suggestion that concatenates all step messages into a single column

😎

SELECT

sj.name

,sj.enabled

,STUFF(

(

SELECT

NCHAR(92) + JH.message

FROM dbo.sysjobhistory JH

WHERE sj.job_id = JH.job_id

ORDER BY JH.step_id ASC

FOR XML PATH(''),TYPE

).value('(./text())[1]','NVARCHAR(MAX)'),1,1,N'') AS MSG_STR

from sysjobs sj

WHERE sj.enabled = 1;

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 5

How I do find all the jobs which have failed today and the failed job when they ran the last time they were scheduled.

Ex 1:All jobs if scheduled today - list of failed jobs for today

Ex 2:Lets say for jobs scheduled on a weekly/monthly basis say every Sunday (and today is wednesday)

If they failed on Sunday.I should also get them under the failed jobs list.

The result should have JobName,messages concatenated from all steps,run_date + run_time under 1 column rundatetime.

Thanks

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

sqlnewbie17 (11/10/2016)
How I do find all the jobs which have failed today and the failed job when they ran the last time they were scheduled.
Ex 1:All jobs if scheduled today - list of failed jobs for today
Ex 2:Lets say for jobs scheduled on a weekly/monthly basis say every Sunday (and today is wednesday)
If they failed on Sunday.I should also get them under the failed jobs list.
The result should have JobName,messages concatenated from all steps,run_date + run_time under 1 column rundatetime.
Thanks

Quick suggestion, start here BOL: dbo.sysjobhistory

😎