Need formatted output

Question

Need formatted output

abhas

SSCarpal Tunnel

Points: 4681
More actions
June 8, 2016 at 2:34 am

#311054

Hi Friend,
Sample table and data is as below.
create table #testdata(id int ,name varchar(20),Dept varchar(20))
insert into #testdata (id,name,Dept) values ( 1,'Jack','IT')
insert into #testdata (id,name,Dept) values ( 2,'Jhon','IT')
insert into #testdata (id,name,Dept) values ( 3,'Ram','CHEM')
insert into #testdata (id,name,Dept) values ( 4,'Shaym','CHEM')
insert into #testdata (id,name,Dept) values ( 5,'Mike','CHEM')
insert into #testdata (id,name,Dept) values ( 6,'Robort','MATH')
insert into #testdata (id,name,Dept) values ( 7,'David','MATH')
insert into #testdata (id,name,Dept) values ( 8,'Rocky','MATH')
From above sample data, I want to display out using 10 astric("*") after changing a group as below: Is it possible? Please help.
CHEM
3Ram
4Shaym
5Mike
**********
IT
1Jack
2Jhon
**********
MATH
6Robort
7David
8Rocky
Thanks,
Abhas

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Abhas

That's a task usually best done in the presentation layer. Reporting Services would be good for something like that. If you must do it in T-SQL, please will you tell us where and how you will be presenting the data - web page, text file, spreadsheet, other?

John

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Thanks John,

The data will be exported into flat file. Is it possible to do in using T-SQL only?

Abhas.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, but it's certainly not pretty and probably not efficient on large data sets.

WITH Numbered AS (

SELECT

CAST(id AS char(2)) + ' ' + name AS Concatenated

,Dept

,ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY id) AS RowNo

,DENSE_RANK() OVER (ORDER BY Dept) AS DeptNo

FROM #testdata

)

, Separated AS (

SELECT

Concatenated

, RowNo

,DeptNo

FROM Numbered

UNION ALL

SELECT

Dept

,0

,DeptNo

FROM Numbered

WHERE RowNo = 1

UNION ALL

SELECT

'*****************'

,1000 -- arbitrarily large number

,DeptNo

FROM Numbered

WHERE RowNo = 1

)

SELECT TOP (SELECT COUNT(*) - 1 FROM Separated) -- remove final separator

Concatenated AS [ ]

FROM Separated

ORDER BY

DeptNo

,RowNo

John

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 4

Thanks John.

Its perfectly working for me.

I appreciate for your quick response. Thank you so Much...

Regards,

Abhas.

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

If exporting to a flat file, it doesn't have to be on "individual rows", just add some line feeds.

SELECT Dept + char(10) +

( SELECT CAST( id AS varchar(10)) + ' ' + name + char(10)

FROM #testdata i

WHERE i.Dept = o.Dept

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

'*****************'

FROM #testdata o

GROUP BY Dept;

For an accurate representation, select "Results to Text".

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Brilliant! I knew there'd be a better way of doing it. You're returning that final separator, but that can be either ignored or easily got rid of.

John