SQL Server Puzzle | Count & Filter Puzzle

Well I got this puzzle on a popular site experts exchange. In this puzzle, You have to write a single SELECT statement (If possible).

1. Group By Data on Part column
2. Get Count of Distinct Parts
3. Get Sum of Completed Parts
4. Show Data in single line in asked format

The Challenge is to do it in a single select.

Please check out the sample input and the expected output below-

Sample Input

Part Completed
PartA 0
PartA 1
PartB 0
PartC 0
PartC 0

Expected Output

PartA*2(C) PartB*1 PartC*2

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

Script | use below script to create table & insert some sample data

 ``` -- Create Table #TempTable ( Part varchar(50) NULL, Completed bit NULL ) ON [PRIMARY] GO INSERT INTO #TempTable (Part,Completed) VALUES ('PartA',0), ('PartA',1), ('PartB',0), ('PartC',0), ('PartC',0) -- ```

Solution 1 | Pawan

 ``` -- IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable Create Table #TempTable ( Part varchar(50) NULL, Completed bit NULL ) ON [PRIMARY] GO INSERT INTO #TempTable (Part,Completed) VALUES ('PartA',0), ('PartA',1), ('PartB',0), ('PartC',0), ('PartC',0) DECLARE @FinalOutput AS VARCHAR(1000)='' SELECT @FinalOutput = @FinalOutput + a FROM ( SELECT DISTINCT CONCAT ( PART ,'*' ,COUNT(*) OVER (PARTITION BY Part) , CASE WHEN SUM(CAST(Completed AS smallint)) OVER (PARTITION BY Part) > 0 THEN CONCAT('(','C',')') ELSE '' END ,' ' ) a FROM #TempTable )e SELECT @FinalOutput -- ```

Solution 2 | Experts Exchange

 ``` -- IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable Create Table #TempTable ( Part varchar(50) NULL, Completed bit NULL ) ON [PRIMARY] GO INSERT INTO #TempTable (Part,Completed) VALUES ('PartA',0), ('PartA',1), ('PartB',0), ('PartC',0), ('PartC',0) --count of records SELECT Part, COUNT(*) AS NumberOfRecords FROM #TempTable GROUP BY Part --completed parts SELECT Part FROM #TempTable WHERE Completed = 1 --joined SELECT a.Part + '*' + a.NumberOfRecords + a.Completed FROM ( SELECT CONVERT(Varchar(50),t.Part) AS Part, CONVERT(VarChar(3),COUNT(*)) AS NumberOfRecords, CASE WHEN t1.part IS NOT NULL THEN '(C)' ELSE '' END AS Completed FROM #TempTable t LEFT JOIN (SELECT Part FROM #TempTable WHERE Completed = 1) t1 ON t.Part = t1.Part GROUP BY t.Part, t1.Part ) a ORDER BY a.Part --your answer declare @t nvarchar(200) set @t='' Select @t= @t + ' ' + a.Part + '*' + a.NumberOfRecords + a.Completed FROM ( SELECT CONVERT(Varchar(50),t.Part) AS Part, CONVERT(VarChar(3),COUNT(*)) AS NumberOfRecords, CASE WHEN t1.part IS NOT NULL THEN '(C)' ELSE '' END AS Completed FROM #TempTable t LEFT JOIN (SELECT Part FROM #TempTable WHERE Completed = 1) t1 ON t.Part = t1.Part GROUP BY t.Part, t1.Part ) a ORDER BY Part select @t -- ```

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com