Group/Format Data to Display Value Only Once per Group

Question

Post reply

Group/Format Data to Display Value Only Once per Group

Shierry

SSC Enthusiast

Points: 117
More actions
September 16, 2011 at 9:15 am

#257394

Good morning,
I'm relatively new to SSIS, and I have come across an output format question that I am not quite sure how to handle. See simple example below:
TableA
_______
ID NAME AGE
1 John 10
2 Sam 15
3 Bob 20
TableB
_______
ID Fruit Day
1 apple 1
1 orange 1
2 apple 3
3 banana 3
1 pear 2
So, say my query looks like this:
Select A.name, B.Fruit, B.Day From TableA A join TableB B on A.ID=B.ID where A.ID = 1
And my output looks like this:
John apple 1
John orange 1
John pear 2
I'm using an SSIS package to generate a flat file, and I would like the data to be formatted like:
John|apple|1|orange|1|pear|2|
or
John|
apple|1|
orange|1|
pear|2|
Basically, just display "John" one time for the group instead of showing it on each line.
I've tried a few different joins, merges, rollups, etc in SQL unsuccessfully. So, my question is can this transformation be done in SSIS? Would it be simpler to do this in SQL prior to the transformation stage in SSIS? I apologize if this seems trivial, I've been looking at it for a couple of days and nothing has come to mind. Thanks in advance for your help!

Viewing 9 posts - 1 through 9 (of 9 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

I'm not sure why you would try to do this in SSIS.

Sure I can see this in SSRS on a report - but not in a flat file export.

If you were exporting it as XML, that makes a bit of sense.

I would do this on the SQL end before trying to do it in SSIS.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Shierry SSC Enthusiast Points: 117 More actions · Answer 2

I thought about SSRS, but I needed the file pipe delimited which isn't a default option in SSRS. I'll see if there is a way to add that option. I was attempting to do it in SQL (without much luck), but I read it wouldn't be very efficient to do it in SQL anyway. So, I moved on to SSIS, which wasn't my first choice, but I was just wondering if it could be done. Thanks for your response!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

not sure why you are using SSIS...a TSQL possible solution below.

have a play around with this code.....seems therre are several posts recently relating to "fruit"....are you all on the same course??? :ermm:

-- create some data

with produce (id,fruit, varieties)

as (

SELECT 101,'Apple', '3' UNION ALL

SELECT 101,'Banana', '2' UNION ALL

SELECT 102,'Orange', '1' UNION ALL

SELECT 103,'Melon' ,'2' UNION ALL

SELECT 103,'Grape' ,'1' UNION ALL

SELECT 104,'Apple' ,'1' UNION ALL

SELECT 105,'Banana' ,'1' UNION ALL

SELECT 105,'Kiwi' ,'1' UNION ALL

SELECT 105,'Tangerine' ,'1' UNION ALL

SELECT 106,'Mango' ,'3' UNION ALL

SELECT 106,'Melon' ,'2'

)

--query as follows

SELECT id,

Stuff((SELECT ',' + fruit + ' (' + varieties + ') : '

FROM produce p2

WHERE p1.id = p2.id

ORDER BY p2.fruit --- sort by Fruit name

FOR XML PATH('')), 1, 1, ' ')

FROM produce p1

GROUP BY id

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

J Livingston SQL (9/16/2011)
not sure why you are using SSIS...a TSQL possible solution below.
have a play around with this code.....seems therre are several posts recently relating to "fruit"....are you all on the same course??? :ermm:
...

If not same course, then same book.

Anyway, here's a slight mod to the final select in the query.

SELECT id,

Stuff((SELECT '| ' + fruit + ' (' + varieties + ') '

FROM produce p2

WHERE p1.id = p2.id

ORDER BY p2.fruit --- sort by Fruit name

FOR XML PATH('')), 1, 1, ' ')

FROM produce p1

GROUP BY id

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Jason

now you are spoon feeding .....:-P:-P:-P:-P

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

J Livingston SQL (9/16/2011)
Jason
now you are spoon feeding .....:-P:-P:-P:-P

Yeah - I know.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Shierry SSC Enthusiast Points: 117 More actions · Answer 7

Ha, no course or book. I work at a hospital and couldn't post actual data for obvious HIPAA reasons. It seems to me anytime anyone posts generic database questions, fruit is always a popular choice. I have never used the Stuff() function before, and it looks very promising! I will give it a go, and let you know the results. I appreciate both of your help with this!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

good luck.....post back if you get problems

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Group/Format Data to Display Value Only Once per Group

Cookies on SQLServerCentral