group by column as sub header

Question

Post reply

group by column as sub header

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
January 28, 2014 at 6:51 am

#303507

Hi,
Thanks for your help in advance, much appreciated.
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (1, N'a', N'regtaergerga')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (2, N'a', N'fdbbfdsgf')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (3, N'a', N'fgfdfgadfg')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (4, N'b', N'tttryr')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (5, N'b', N'grge')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (6, N'c', N'thyh')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (7, N'c', N'nnyt')
GO
SET IDENTITY_INSERT [dbo].[Table_1] OFF
GO
To:
a
regtaergerga
fdbbfdsgf
fgfdfgadfg
b
tttryr
grge
c
thyh
nnyt
Just as a single varchar(max) field.
Thank you very much for the help,
Oliver

Viewing 13 posts - 1 through 12 (of 12 total)

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

pietlinden SSC Guru Points: 63421 More actions · Answer 1

If you do this in Reporting Services, it's simple.

create a data source that points to your database,

then a dataset that points to this query

then add a tablix and add a grouping by your column

or does it need to be pure T-SQL?

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

Hi,

Yes unfortunately it needs to be pure T-SQL. Sorry I know it's very easy in SSRS but for the task I need it for this isnt appropriate.

Thanks,

Oliver

KS Hall of Fame Points: 3323 More actions · Answer 3

Hope this helps. You might want to refer this article

;WITH CTE AS

(

SELECT DISTINCT Header FROM Table_1

)

SELECT CONVERT(VARCHAR(MAX),Header + CHAR(10) +

STUFF((

SELECT CHAR(10) + Citation

FROM Table_1

WHERE Header = CTE.Header

ORDER BY id

FOR XML PATH(''),

TYPE).value('.','varchar(max)'),1,1,'')) AS [RequiredData]

FROM CTE

ORDER BY Header

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 4

Hi,

Thanks for the help, however this isnt what I am after, I essentially want the group by field (column 2) to be on top (as a title grouping) of column 3

e.g.

a

regtaergerga

fdbbfdsgf

fgfdfgadfg

b

tttryr

grge

c

thyh

nnyt

Many Thanks,

Oliver

Nevyn SSChampion Points: 14210 More actions · Answer 5

Not very elegant, but:

WITH myheads AS (select header, 0 AS orderfield from table_1 group by header)

, myfeet AS (select header,CAST ('' AS VARCHAR) AS citation ,2 AS orderfield FROM myheads)

, mybody AS (select header,citation, 1 AS orderfield FROM table_1)

, myunion AS ( SELECT header, header AS citation, orderfield FROM myheads

UNION ALL

SELECT header, citation, orderfield FROM myfeet

UNION ALL

SELECT header, citation, orderfield FROM mybody)

SELECT citation

FROM myunion

ORDER BY header,orderfield,citation

EDIT: noticed that you sort by ID instead of citation, so:

WITH myheads AS (select header, 0 AS orderfield from table_1 group by header)

, myfeet AS (select header,CAST ('' AS VARCHAR) AS citation ,2 AS orderfield FROM myheads)

, mybody AS (select header,citation,ID, 1 AS orderfield FROM table_1)

, myunion AS ( SELECT header, header AS citation,0 AS ID, orderfield FROM myheads

UNION ALL

SELECT header, citation,0, orderfield FROM myfeet

UNION ALL

SELECT header, citation,ID, orderfield FROM mybody)

SELECT citation

FROM myunion

ORDER BY header,orderfield,ID

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

I had a similar option to Nevyn but using a subquery.

SELECT citation

FROM(

SELECT header,

CAST( header AS varchar(200)) AS citation,

1 AS roworder

FROM Table_1

GROUP BY header

UNION ALL

SELECT header,

citation,

2 AS roworder

FROM Table_1

UNION ALL

SELECT header,

'' AS citation,

3 AS roworder

FROM Table_1

GROUP BY header

)x

ORDER BY header, roworder

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

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 7

Many Thanks Nevyn and Luis I get the concept now, I really appreciate your help.

Thank You,

Oliver

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

Check the first link in my signatures for another concept you can apply to this (the CROSS APPLY VALUES approach to UNPIVOT).

SELECT citation

FROM

(

SELECT b.citation, a.header, a.rn, rn1

,rn2=ROW_NUMBER() OVER (PARTITION BY a.header, b.citation ORDER BY (SELECT NULL))

FROM

(

SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY header ORDER BY citation)

FROM Table_1 a

) a

CROSS APPLY

(

VALUES (1e6, ''),(1, header), (2, citation)

) b (rn1, citation)

) a

WHERE rn2 = 1

ORDER BY header, rn1, rn DESC

Avoids all those ugly UNIONs.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 9

Thanks Dwain,

Will give this a try and try the unpivot alternative, looks good.

Is performance better than unpivot?

Cheers

Oliver

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

oliver.morris (1/29/2014)
Thanks Dwain,
Will give this a try and try the unpivot alternative, looks good.
Is performance better than unpivot?
Cheers
Oliver

There's a performance comparison in the linked article, but the short answer is probably.

Possibly better than the UNION ALL methods also because less table scans would be involved (although the sorts generated by ROW_NUMBER() might make up for that).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

oliver.morris (1/28/2014)
Hi,
Thanks for your help in advance, much appreciated.
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (1, N'a', N'regtaergerga')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (2, N'a', N'fdbbfdsgf')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (3, N'a', N'fgfdfgadfg')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (4, N'b', N'tttryr')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (5, N'b', N'grge')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (6, N'c', N'thyh')
GO
INSERT [dbo].[Table_1] ([ID], [header], [citation]) VALUES (7, N'c', N'nnyt')
GO
SET IDENTITY_INSERT [dbo].[Table_1] OFF
GO
To:
a
regtaergerga
fdbbfdsgf
fgfdfgadfg
b
tttryr
grge
c
thyh
nnyt
Just as a single varchar(max) field.
Thank you very much for the help,
Oliver

Classic problem deserves the classic "Black Arts" solution. Run the following in the "Results to Text" mode.

--===== Classic "easy" method for solving this problem

-- Run this in the "Results to Text" mode rather than the "Results to Grid" mode.

SELECT CASE WHEN GROUPING(Citation) = 0 THEN Citation ELSE CHAR(10)+Header END

FROM dbo.Table_1

GROUP BY Header, Citation WITH ROLLUP

HAVING GROUPING(Header) = 0

ORDER BY Header,GROUPING(Citation) DESC, Citation

;

Run this to find out why it works...

--===== Study the output from this to understand how it works.

-- Run this in the "Results to Grid" mode

SELECT Header

,Citation

,GroupingHeader = GROUPING(Header)

,GroupingCitation = GROUPING(Citation)

FROM dbo.Table_1

GROUP BY Header,Citation WITH ROLLUP

;

In days of old when knights were bold,

And Rownum was not invented,

He wrappped a group around his code

And extra sorts were prevented. 😛

--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)

Nevyn SSChampion Points: 14210 More actions · Answer 12

Nevyn

SSChampion

Points: 14210

February 2, 2014 at 7:51 am

#1685343

Well played, sir.