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

How can you combine the columns of two queries together?
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpiv...
----
There are multiple ways to combine the columns of queries together.
One way is by using a correlated query. Another way is to add a second calculation.
If you want to do this as a Practice Activity, then here's the original code.
The original query is (with a minor modification, so I can post it here):
SELECT type_desc, COUNT(*) AS NumberOfObjects
FROM sys.objects
GROUP BY type_desc
SELECT type_desc, COUNT(*) AS NumberOfObjectsIn2022
FROM sys.objects
WHERE YEAR(create_date) = 2022
GROUP BY type_desc
----
Links to my website are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): idodata.com/database-fundament...
SQL Server Essential in an Hour: idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer - certified associate: idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: idodata.com/dp-900-microsoft-a...

[@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