October 1, 2010 at 12:01 am
i have table which show data like
Position Name
A John
A adam
A eddie
B scott
B james
and i want to display like this
Position name
A John , Adam, Eddie
B scott, james
Thanks
October 1, 2010 at 6:15 am
--// Please try the below script
------------------------------------------------------------------------------------------------
CREATE TABLE tYourTableName
(
Position CHAR(1),
Name VARCHAR(30)
)
INSERT INTO tYourTableName VALUES('A','John')
INSERT INTO tYourTableName VALUES('A','adam')
INSERT INTO tYourTableName VALUES('A','eddie')
INSERT INTO tYourTableName VALUES('B','scott')
INSERT INTO tYourTableName VALUES('B','james')
DECLARE @tPosition AS TABLE
(
Id TINYINT IDENTITY(1, 1),
Position CHAR(1)
)
DECLARE @tPositionName AS TABLE
(
Position CHAR(1),
Name VARCHAR(100)
)
DECLARE @SqlStr VARCHAR(100)
DECLARE @Position CHAR(1)
DECLARE @Count TINYINT
DECLARE @Counter TINYINT
SET @SqlStr = ''
INSERT INTO @tPosition(Position) SELECT DISTINCT Position FROM tYourTableName
SET @Count = SCOPE_IDENTITY()
SET @Counter = 1
WHILE(@Counter <= @Count)
BEGIN
SELECT @Position = Position
FROM @tPosition
WHERE Id = @Counter
SET @SqlStr = NULL
SELECT @SqlStr = ISNULL(@SqlStr + ', ', '') + COALESCE(Name, @SqlStr)
FROM tYourTableName
WHERE Position = @Position
INSERT INTO @tPositionName(Position, Name)
SELECT @Position, @SqlStr
SET @Counter = @Counter + 1
END
SELECT Position, Name FROM @tPositionName
DROP TABLE tYourTableName
------------------------------------------------------------------------------------------------
_____________________________________________
One ounce of practice is more important than tonnes of dreams
October 1, 2010 at 6:55 am
Thanks ...yes thats how i want ..thanks a lot
October 1, 2010 at 7:21 am
I'd have to really, really really disagree with this as a method for solving the OP's issue.
First, it's Dynamic SQL which can cause security and performance issues down the road.
Second, you only declared @StrSQL as a varchar(100) great for the current issue, but what happens when he needs to group a few more names?
Third, it uses iteration which is slow and won't scale.
Fourth, this is in a SSRS forum, there are better ways to do this in reporting services. Using proper grouping and the Join function will make this easy.
IF you really need to do this in TSQL I'd suggest you look at the PIVOT function in BOL additionally, have a look at the following article by Jeff Moden, Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]. He covers a variety of ways to make this set based and efficient in that article series.
-Luke.
October 2, 2010 at 1:37 am
Dear Luke,
Thank you for your comment. When I saw makham01’s question, the only thing came into my mind is “COALESCE”. So I tried to get the result over it. If I did not submit the solution, I will not come to know what the problems with the code which you mentioned are. So next time when I write the code I do try to care that also, even makham01 also can do some modifications or try other logic what you have suggested. Thanks Luke.
Regards,
Shaiju CK
_____________________________________________
One ounce of practice is more important than tonnes of dreams
October 2, 2010 at 3:17 am
I agree with Luke that Shaiju CK's implementation isn't that good. However, a PIVOT won't work unless there are the same # of names.
So, here's a working solution:
DECLARE @tYourTableName TABLE (
Position CHAR(1),
Name VARCHAR(30)
);
INSERT INTO @tYourTableName
SELECT 'A','John' UNION ALL
SELECT 'A','adam' UNION ALL
SELECT 'A','eddie' UNION ALL
SELECT 'B','scott' UNION ALL
SELECT 'B','james' ;
SELECT DISTINCT
Position,
Name = stuff((SELECT ', ' + Name
FROM @tYourTableName t2
WHERE t2.Position = t1.Position
FOR XML PATH(''),TYPE).value('.','varchar(max)')
,1,2,'')
FROM @tYourTableName t1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2010 at 5:14 am
Hi Wayne,
I have learned something new today. Thank you very much 🙂
Regards,
Shaiju CK
_____________________________________________
One ounce of practice is more important than tonnes of dreams
October 2, 2010 at 9:58 am
Glad to be of assistance! 😀
Seriously, I do like Luke's suggestion of using grouping and the JOIN function within SSRS. The solution I posted might reduce network traffic and server load. So, test to see which will work best for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2010 at 5:37 pm
Thanks Wayne,
Both the solutions are working , but the XML path solution best fit my situations.
Thanks once again.
Regards
ALi
October 5, 2010 at 1:05 pm
Hi
Use join in the report expressions.If u want to do in report side
Thanks,
Veeren.
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
October 6, 2010 at 3:17 pm
Hi veeren4urs, can you give examples of join on report side ?
October 6, 2010 at 8:42 pm
A couple of people mentioned using Join to solve the problem on the GUI side but I'd be curious to know how to implement it. I had this problem a while back and came up with the following solution. It works but if someone has a simpler and more elegant solution, please share.
(1) Add this to your report code section:
Private curString As String = String.Empty
Private retString As String = String.Empty
Private curBreakString As String = String.Empty
Public Function ConcatString(nextString As String, breakString As String) As String
If breakString <> curBreakString Then
curBreakString = breakString
retString = String.Empty
curString = String.Empty
End If
If curString <> nextString Then
curString = nextString
If retString = String.Empty Then
retString = nextString
Else
retString = retString & ", " & nextString
End If
End If
Return retString
End Function
(2) Create a DataSet with the following as Query (borrowed from WayneS's test code above):
DECLARE @tYourTableName TABLE (
Position CHAR(1),
Name VARCHAR(30)
);
INSERT INTO @tYourTableName
SELECT 'A','John' UNION ALL
SELECT 'A','adam' UNION ALL
SELECT 'A','eddie' UNION ALL
SELECT 'B','scott' UNION ALL
SELECT 'B','james' ;
SELECT Position, Name FROM @tYourTableName
(3) Create a tablix with two columns and bind it to your DataSet. In "Row Groups" you might see a group already created called "Details" if you have SSRS2008. Modify it so that it group by Position and rename it "PositionGrouping". If you don't see "Details", just create a new grouping and set parameters as above. Just make sure you only have one grouping.
(4) Make sure the first column of your tablix show the Position. In the second column, enter the following expression: =RunningValue(Code.ConcatString(Fields!Name.Value, Fields!Position.Value), Last, "PositionGrouping")
I have a working *.rdl if anyone is interested.
October 7, 2010 at 7:42 am
Vince and all, I'd never used Join to concatenate the results from a dataset before, just kinda assumed it would work based on how it works to join the strings of a multivalue parameter. After playing around with it and some googling, it looks like Vince's solution is the way to go if you want to keep it in the GUI. Apparently, when you group a dataset it doesn't return an array as I imagined that it would so there's no way to feed the join() function without custom code.
Thanks for the code vince.
-Luke.
October 7, 2010 at 8:04 am
I hope that one day SSRS would come with a string aggregation feature where the SUM() function would work on strings as well as numeric values. The result would be a comma-delimited string containing the component string values. If any of you have used Hyperion, you would know how easy it was to do this. SSRS is much better with the presentation aspect but I kinda miss Hyperion's powerful pivot features.
October 7, 2010 at 8:23 am
If you feel that strongly about it, search the connect site and see if it has already been requested, if not request it and see if other people feel it's valuable as well. It seems to me though that what you are asking about is string concatenation and not addition (what the sum function does).
I personally think the join function should be implemented so that it can take a column and dataset name (and grouping level), or as part of the runningvalue aggregate functions, but alas it's not so.
-Luke.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply