Practice Activity: Adding the column from one query into another query in SQL Server.

[@user-of3sh4cg7q]

Which keyboard do you use?Can you please tell the model
Thanks

[@SQLServer101]

Hi User. It is a Realforce. Phillip

[@chiroop5541]

Hi, Its a great platform to learn and enhance SQL skillset. Here I'm struggling to convert below SAS code into T-SQL, could you please help me in this -
SAS Code:
Data A2; Set A1;
nwords = CountW(HCC_LIST, ',');
i = 1;
ind = 'N';
Do i = 1 To nwords;
If HCC = '' Then Do;
ind = 'Y';
unique_hcc = 'N';
End;
Else if Scan(HCC_LIST, i, ',') Trim(HCC) Then Do;
ind = 'Y';
unique_hcc = 'N';
End;
If i > nwords And ind = 'N' Then Do;
unique_hcc = 'Y';
Drop nwords i ind;
Output;
End;
Run;
Sample Input Data (A1):
ID HCC HCC_LIST
123 35 16,128,35,185
123 16,128,35,185
123 116 16,128,35,185
123 128 16,128,35,185
Sample Output Data (A2):
ID HCC HCC_LIST Unique_HCC
123 35 16,128,35,185 N
123 16,128,35,185 N
123 116 16,128,35,185 Y
123 128 16,128,35,185. N

[@chiroop5541]

Sorry correction: Else if Scan(HCC_LIST, i, ',') = Trim(HCC) Then Do;

[@SQLServer101]

Hi Chiroop. Thank you for your question. Here is the T-SQL code:
-- Create a temporary table A2
SELECT *,
nwords = LEN(HCC_LIST) - LEN(REPLACE(HCC_LIST, ',', '')) + 1,
i = 1,
ind = 'N',
unique_hcc = 'N'
INTO #A2
FROM A1;
-- Loop through the records in the temporary table
DECLARE @maxI INT, @currentI INT
SELECT @maxI = MAX(i) FROM #A2;
SET @currentI = 1;
WHILE @currentI nwords and ind = 'N'
UPDATE #A2
SET unique_hcc = 'Y'
WHERE i > nwords AND ind = 'N';
-- Drop unnecessary columns and select the final result set
ALTER TABLE #A2
DROP COLUMN nwords, i, ind;
SELECT *
FROM #A2;
-- Clean up the temporary table
DROP TABLE #A2;
-- Phillip