Today a good friend of mine Hardik Vadher asked me a very interesting question related to SQL. I got his email in the evening when I was in a shopping mall. The whole time I was trying to solve his question in my mind while driving back to home. Soon I realised that I already have a function named ufn_split_string_row_col created a long back, which can easily take care of this by tweaking the query a little bit. It is very simple but I thought to write a blog on it so that any one having same or related kind of question in future can get help from this.
--Data insertion IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp CREATE TABLE #TEMP (QID INT, TNM VARCHAR(10), CDT VARCHAR(10)) INSERT INTO #TEMP SELECT 1 , 'Q1' , 'DEC1' union all SELECT 1 , 'Q2' , 'DEC2' union all SELECT 1 , 'Q3' , 'DEC3' union all SELECT 2 , 'Q1' , 'DEC1' union all SELECT 2 , 'Q2' , 'DEC2' union all SELECT 2 , 'Q3' , 'DEC5' union all SELECT 3 , 'Q1' , 'DEC3' union all SELECT 3 , 'Q2' , 'DEC5' union all SELECT 4 , 'Q3' , 'DEC2' --Original SELECT * FROM #TEMP --Required resultset ; WITH CTE AS (SELECT DISTINCT QID FROM #TEMP) , CTE2 AS (select QID , STUFF((SELECT '|' + b.TNM + '|' + b.CDT FROM #TEMP b WHERE b.QID = a.QID FOR XML PATH('') ), 1, 1, '') AS TXT FROM cte a) SELECT c.QID, d.* FROM CTE2 AS c CROSS APPLY [ufn_split_string_row_col] (c.TXT, '~', '|') d