data
ID CommentID Comments
1 1 'app'
1 2 'le'
2 1 'or'
2 2 'an'
2 3 'ge'
3 1 'banana'
results want to get
1 apple
2 orange
3 banana
I was thinking to using the PIVOT, but as the number of row is unknown, I think it can't be used.
any help is appreciate!
I would seriously consider not doing this in SQL. It will be much easier to do in the presentation layer (and you won't have any issues with the size of the data, either)
You could build a user defined function and cursor through the comments ordered by the commentID (assuming that is the order) also, but unless you actually need the results in another SQL statement, using the presentation layer would be the best way to go
|||I understand that you are using SQL Server 2005..
Here CTE is best solution rather than UI..
In UI you have to Loop or need to use DataViews.. It is bit expensive than SQL query..
Try the following Query it will help you..
create table comments
(
Id int,
CommentId int,
Comments varchar(100)
)
Go
Insert into comments values (1,1,'app')
Insert into comments values (1,2,'le')
Insert into comments values (2,1,'or')
Insert into comments values (2,2,'an')
Insert into comments values (2,3,'ge')
Insert into comments values (3,1,'banana')
Go
WITH JoinComment (Id,CommentId,Comments,MaxId)
as
(
Select A.id,A.CommentId,convert(varchar(8000),Comments) Comments,Max(commentId) Over (partition By Id) as maxId From Comments a
Union All
Select A.Id, A.CommentId, A.Comments + B.Comments as Comments,A.CommentId
From
Comments A Inner Join JoinComment B
On (A.CommentId+1)=B.CommentId
And A.CommentId <> B.CommentId
and B.CommentId = B.maxId
And A.Id=B.Id
)
Select Id,CommentId,Comments from JoinComment
Where CommentId=MaxId And CommentId=1
NOCOUNT ON
DECLARE
@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)
INSERT
INTO @.T SELECT DISTINCT CommentID FROM comments
DECLARE
@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)
DECLARE @.i AS int
SET @.i=1
WHILE @.i <20
BEGIN
INSERT INTO @.T1 SELECT @.i
SET @.i=@.i+1
END
DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)
SET @.y = (SELECT MIN(y) FROM @.T)
SET @.cols = N''
SET @.cols2 = N''
WHILE @.y IS NOT NULL
BEGIN
SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'
SET @.cols2 = @.cols2 + N'+ coalesce(['+CAST(@.y AS nvarchar(20))+N'],'''')'
SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)
END
SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))
SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)
DECLARE @.sql AS nvarchar(MAX)
SET @.sql = N'SELECT ID' + N',(' +@.cols2 + N') AS newColumn FROM (SELECT ID, CommentID, Comments FROM comments) as t
PIVOT (min(comments) FOR CommentID IN(' + @.cols + N')) AS pvt'
EXEC sp_executesql @.sql|||
Hi Terrence,
Here is my solution.
I assume that the comments table named as "z"
Also note that I'm using a user defined function named dbo.Split() which you can find its code from
http://www.kodyaz.com/forums/489/ShowThread.aspx#489
declare @.s varchar(100), @.i tinyint, @.t tinyint
select @.t = 0, @.i = min(id), @.s = CAST(@.i as varchar(5)) + '-' from z
select
@.t = case when @.i = id then 0 else 1 end,
@.s = coalesce(@.s + case when @.t = 1 then ';' + CAST(id as varchar(5)) + '-' else '' end + comments , ''),
@.i = id
from z
order by id, commentid
-- select @.s
select
SUBSTRING(strval,0, CHARINDEX('-', strval)) AS Id,
SUBSTRING(strval, CHARINDEX('-', strval) + 1, LEN(strval) - 2) AS Comment
from dbo.Split(@.s,';')
Eralper
http://www.kodyaz.com
|||As Louis pointed out, you will be better off doing this on the client side. Any server solution will perform poorly and look complicated. Is there any reason why you are storing the comments as multiple rows? How do the comments gets split across rows? What happens if the comments get edited? Do you delete all the rows and reenter them? It is best you redesign the schema to use a simple comments column of varchar(8000), varchar(max), nvarchar(4000) or text as appropriate. Suggesting a SQL solution is easy actually but it is not the right way. As you suggested, you can use PIVOT in SQL Server 2005 without dynamic SQL by fixing the maximum number of comment fragments.|||
Here is my table schema
ApplicationComment Table
-AppId int (Key)
-CommentId int(Key)
-Comments
-ModifiedDate
-ModifiedBy
What I am doing is creating a stored procedure(sp) for a report using reporting services, so I think concate the comments in a sp (rather than the presentation layer) is more appropriate.
Here is the sample data
1,1, 'This is the first comments',.....
1,2, 'This is the second comments for the application Id 1',.....
1,3,'The application is closed',.....
In the front end, the modified date, modified by and and comments are shown in a datagrid.
01/01/2007 Tester1 'This is the first comments'
01/02/2007 Tester2 'This is the second comments for the application Id 1'
In the comments summary report, what I want is this
Application ID , Comments
1, This is the first comments
This is the second comments for the application Id 1
The application is closed
2, .....
Umachandar, I am more than happy to take any suggestion that you may have... Thanks.
|||
I don't know enough about Reporting Services to comment on it's features. But these type of reports are trivial to generate in Crystal Reports for example (one I am familiar with and used in the past). You can just issue a query like:
select a.AppId, a.Comments
from ApplicationComment as a
order a.AppId, a.CommentId
And suppress repeating values in the report. That is all there is to it. You can also do simple grouping to provide a header and several detail rows kind of report. Anyway, it seems like you should ask in the Reporting Services newsgroup about how to generate such a formatted report.
You can use SQL to generate the result set like:
select pa.AppId
, pa.[1] + coalesce(pa.[2], '') + coalesce(pa.[3], '')...... as Comments
from (select AppId, CommentId, Comments from ApplicationComment) as a
pivot (min(a.Comments) for a.CommentId in ([1], [2], [3], ....
/* fix some maximum number here. 100 or 1000 it doesn't matter*/
)) as pa
order by pa.AppId
Note that you may have to cast one of the expressions for the Comments column to varchar(max) or nvarchar(max) if the maximum length of the comments can exceed 8000 bytes. Also, the performance of the query will be poor due to the string concatenations and it will be directly proportional to the number of rows in the table & number of comments per id.
|||Here is my solution using UDF as it looks more clearer for me, but as most of the expert explains don't do in the server side.
I finally put that in the presentation layer because of the poor perfermance.
create function dbo.uf_ConcateComments(
@.ApplicationId INT
)
returns varchar(max)
as
begin
declare
@.Return as VARCHAR(max),
@.newline as char(2)
set @.Return = ''
set @.newline = char(13) + char(10)
select
@.Return = @.Return + @.newline + @.newline +
cast(DATEPART(dd,t.CreatedDate)as varchar(2)) + '/' +
cast(DATEPART(mm,t.CreatedDate)as varchar(2)) + '/' +
cast(DATEPART(yyyy,t.CreatedDate)as varchar(4)) + ' ' +
t.CreatedBy + ': ' + t.Comments
from
dbo.ApplicationComment t
where t.ApplicationId = @.ApplicationId
order by
t.CommentID
return (@.Return)
end
GO
In a store procedure, do this.
SELECT
ApplicationId,
dbo.uf_ConcateComments(ApplicationId) as concateComments
from
Application x
ORDER BY
ApplicationId desc
|||
>> I finally put that in the presentation layer because of the poor perfermance.<<
That is the best way, in my opinion, as it is very natural for the presentation layer to pass through each row individually, since this is how you end up doing it anyhow (even if you let some object built into some library handle your data)
