tajrin (10/25/2013)
tnx for the reply, some more questions please:1...Is it possible to get 3 as a final result....ie the total number of different combinations?
Sure no problem. Just roll your code into a cte and then it is pretty simple
;with Combinations as
(
Select COUNT(*) / LEN(x.Vals) TCRuns, x.Vals
FROM
(
SELECT t1.ID as RunID, msv1.Val as Vals
From trial t1
CROSS APPLY
(SELECT
(SELECT t2.Val --+ '|'
FROM trial t2
WHERE t1.ID = t2.ID
ORDER BY t2.Val
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) msv1 (Val)
) x
Group by x.Vals
)
, TotalCount as
(
select COUNT(*) as TotalCount from Combinations
)
select *
from Combinations
cross join TotalCount
2...why did you comment out the pipe separator (|)?
It made it far easier to see the results without the extra noise of a | in middle. There was no logical reason to remove it. If you want/need it just put it back in.
3...Is it possible to achieve the same result without using FOR XML path, because this is kind of slow.
It is certainly possible to do this other ways but I don't know that there is a faster way.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/