Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Matching data between rows and columns ]

Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/10/19/TSQL-Challenge-15-matching-data-between-rows-and-columns.aspx

The puzzle is simple. In this puzzle we were required to find the first and last IDs for consecutive rows with same values of Send and Ack states. Please check out the sample input and expected output for details.

Sample Input

Rower

Row
100
104
101
99
77
20
10

Cols

Col
1
2
3
4
5
6
7
8
9

Expected output

ROW 1 2 3 4 5 6 7 8 9
10 X X X
20 X X X X
77 X X
99 X X X
100 X X X X
101 X
104 X X X X

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


-------


CREATE TABLE Cols (Col INT);

INSERT INTO Cols VALUES (1);
INSERT INTO Cols VALUES (2);
INSERT INTO Cols VALUES (3);
INSERT INTO Cols VALUES (4);
INSERT INTO Cols VALUES (5);
INSERT INTO Cols VALUES (6);
INSERT INTO Cols VALUES (7);
INSERT INTO Cols VALUES (8);
INSERT INTO Cols VALUES (9);

CREATE TABLE Rower (Row INT);

INSERT INTO Rower VALUES (100);
INSERT INTO Rower VALUES (104);
INSERT INTO Rower VALUES (101);
INSERT INTO Rower VALUES (99);
INSERT INTO Rower VALUES (77);
INSERT INTO Rower VALUES (20);
INSERT INTO Rower VALUES (10);

----

UPDATE – 20-Apr-2015 – Solution 1


--

DECLARE @colsPivot AS VARCHAR(MAX) ='',@colsPivotIN AS VARCHAR(MAX) =''
SELECT @colsPivot = STUFF((SELECT ',' + QUOTENAME(Col) FROM cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SELECT @colsPivotIN = STUFF((SELECT ',' +  ' CASE WHEN Row % ' + QUOTENAME(Col) + ' = 0 THEN ''X''  ELSE '''' END '  + QUOTENAME(Col) 
FROM cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
DECLARE @ExStr AS VARCHAR(MAX) = 
							'  ;WITH CTE AS ( select *,ROW_NUMBER() over (order by (select null )) rnk from cols ) 
							,CTE1 AS (
							SELECT * FROM CTE
							PIVOT 
							( max(rnk) FOR col in (' + @colsPivot + ') ) pvt
							) 
							SELECT ROW , ' + @colsPivotIN +  ' from Rower r,CTE1 ORDER BY r.Row'
EXEC (@ExStr)
--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com