Can I flatten out header and detail records with a SQL Statement in Sql Server 2012

Question

I have the following select:

SELECT TOP 1000 [ObjectiveId]
      ,[Name]
      ,[Text]
  FROM [dbo].[Objective]

It gives me

Name    Text
0100    Header1
0101    Detail1
0102    Detail2
0200    Header2
0201    Detail1a
0202    Detail1b

Is there a way I could make a string like this with a ||| divider from the data.

Header1  |||  Detail1
Header1  |||  Detail2
Header2  |||  Detail1a
Header2  |||  Detail1b etc.

The key here is that when the last two digits of name are "00" then it's a header row for following detail rows.

gvee · Accepted Answer · 2013-08-05T10:58:21.443

1

; WITH headers AS (
  SELECT Name
       , Text
  FROM   dbo.Objective
  WHERE  Right(Name, 2) = '00'
)
, details AS (
  SELECT Name
       , Text
  FROM   dbo.Objective
  WHERE  Right(Name, 2) <> '00'
)
SELECT headers.Text + ' ||| ' + details.Text
FROM   headers
 LEFT
  JOIN details
    ON Left(details.name, 2) = Left(headers.name, 2)

edited Aug 05 '13 at 10:58

answered Aug 05 '13 at 10:50

gvee

16,732
35
50

Sorry. Maybe my question was not clear. Your sql looks good but it gives the numbers and not the text. – Aug 05 '13 at 10:57
@Melina updated, try now. Just swapped `headers.Name + ' ||| ' + headers.Name` to `headers.Text + ' ||| ' + headers.Text` – gvee Aug 05 '13 at 10:59
@Melina can you clarify please. What seems to be wrong? Using your sample data this gives the correct output. – gvee Aug 05 '13 at 11:04
I am trying to check it now but keep losing the internet. SOrry – Aug 05 '13 at 11:27
The problem is my data from the first query has 234 rows and when I run your query it comes to over 1000 rows. It should be less than 234. – Aug 05 '13 at 11:30
@Melina then I think the detail and sample data you provided is not comprehensive enough. Please provide a better sample data selection and desired output – gvee Aug 05 '13 at 11:33

score 1 · Answer 2 · answered Aug 05 '13 at 11:58

Query:

SQLFIDDLEExample

SELECT t1.Text + ' ||| ' + t2.Text AS clm
FROM Objective t1
  LEFT JOIN Objective t2
    ON SUBSTRING(t2.Name, 1, 2) = SUBSTRING(t1.Name, 1, 2)
    AND t2.Name not like '%00'
WHERE t1.Name like '%00'

Result:

|                  CLM |
------------------------
|  Header1 ||| Detail1 |
|  Header1 ||| Detail2 |
| Header2 ||| Detail1a |
| Header2 ||| Detail1b |

score 0 · Answer 3 · answered Aug 05 '13 at 10:59

0

Try this:

;WITH Header
AS
(
    SELECT  LEFT([Name], 2) AS HeaderKey,
            [Name], 
            [Text]
    FROM Objective
    WHERE RIGHT([Name], 2) = '00'
),
Detail
As
(
    SELECT  LEFT([Name], 2) AS HeaderKey,
            [Name], 
            [Text]
    FROM Objective
    WHERE RIGHT([Name], 2) <> '00'
)
SELECT Header.[Text] + '|||' + Detail.[Text]
FROM Header
INNER JOIN Detail
    ON Header.HeaderKey = Detail.HeaderKey

answered Aug 05 '13 at 10:59

Steve Ford

7,433
19
40

My source rows are 234 but the output from this is over 1000 rows. – Aug 05 '13 at 11:34

Ejaz · Answer 4 · 2013-08-05T11:35:50.783

-1

select top 1000 A.[Text]+' ||| '+B.[Text]
FROM [dbo].[DILL] as A
Inner join [dbo].[DILL] as B
on substring(A.[Name],3,5) = '00'
and A.[Text] != B.[Text] 
and substring(B.[Name],1,3) = substring(A.[Name],1,3)

EDIT: Forgot to add the last line of code and use the correct JOIN

edited Aug 05 '13 at 11:35

answered Aug 05 '13 at 10:51

Ejaz

145
1
10

1

Firstly, you should really use `JOIN` syntax. Also, your "join" as it stands will return things like `Header1 ||| Header2` – gvee Aug 05 '13 at 10:53
1

You could tell me why this format is bad instead of just saying "join". – Ejaz Aug 05 '13 at 10:58
This gives a very strange output :-( I think something is missing – Aug 05 '13 at 10:58
I just tried it out with the example data you have provided, works fine. – Ejaz Aug 05 '13 at 11:00
1

@Ejaz It's the ANSI standard and much easier to work with when queries get more complex. Also much easier to write OUTER joins with. Reasonable article on the subject: http://www.tek-tips.com/faqs.cfm?fid=5168 – gvee Aug 05 '13 at 11:29
1

@Ejaz Found a SO question on the very topic :) http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – gvee Aug 05 '13 at 11:30
1

https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Nenad Zivkovic Aug 05 '13 at 11:31

Can I flatten out header and detail records with a SQL Statement in Sql Server 2012

4 Answers4