i have a table with 2 columns create table t1 (x varchar(1) , y varchar(1) ) the values are (L, a) (M, a) (N, a) (M, b) (N, b) (L, c) (N, c) (M, d) (L, d) what is a tsql to create a matrix/chart as the following based on it? (pls use fix font/notepad to have characters align) a b c d a M,N L,N L,M b M,N c L,N L d M,L L-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Reports and display formatting are done in the front end, not in the database. --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it

thanks, suppose I have SSRS, how do i report/show the chart? -- IBM has jobs for USA employee to work oversea (India) and get their wages.

DECLARE @tbl TABLE(x varchar(1), y varchar(1)) INSERT INTO @tbl( x, y ) SELECT 'M','a' UNION SELECT 'N','a' UNION SELECT 'M','b' UNION SELECT 'N','b' UNION SELECT 'L','c' UNION SELECT 'N','c' UNION SELECT 'M','d' UNION SELECT 'L','d' ; WITH CTE (Y,X) AS ( SELECT y,(SELECT x+',' FROM @tbl t2 WHERE t1.y = t2.y FOR XML PATH('')) FROM @tbl t1 GROUP BY y) SELECT tbl.y ,t.* from (select * FROM CTE PIVOT (MIN(X) FOR Y IN ([a],[b],[c],[d]))pvt) t cross apply (select y from cte) tbl Not sure if this is what you are looking for Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

well, from the first post, c intersects with a via L,N also c intersects with d with L the query SELECT tbl.y ,t.* from (select * FROM CTE PIVOT (MIN(X) FOR Y IN ([a],[b],[c],[d]))pvt) t cross apply (select y from cte) tbl is interestingly showing c intersects with a with (m, n) and so on... -- IBM has jobs for USA employee to work oversea (India) and get their wages.