Tags

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


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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