Day #69 - TSQL Skills for Data Pipeline and Cleanup Work

Pivot is a key thing when it comes to data preparation tasks, MSSQL pivot without aggregation does need a bit of workaround. Two things we will see in this post

Learning #1 - Script for Insert Data generation from MSSQL tables using SSMS (Hidden Gem in MSSQL)

Step 1 -  Database -> Tasks -> Generate Scripts

Step 2 -  Generate the Database objects (Tables as needed)

Step 3 -  Specify Save to Location, Data only option. After you specify options next step script runs and generates insert statement as needed.

Learning #2- Pivot for Data Preparation scenario

For a given scenario of customer/orders, Pivoting the data for next level of tasks

	DECLARE @cols AS NVARCHAR(MAX),
	@query AS NVARCHAR(MAX)
	SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ItemId)
	FROM CUSTOMER_ORDER
	FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)')
	,1,1,'')
	SELECT @query =
	'SELECT * FROM
	(SELECT
	CUSTOMER_NUMBER,
	ItemId,
	1 as INTC
	FROM CUSTOMER_ORDER)X
	PIVOT
	(
	COUNT(INTC)
	for ItemId in (' + @cols + ')
	) P'
	EXEC SP_EXECUTESQL @query

view raw TSQLPivot.sql hosted with ❤ by GitHub

Happy Learning!!!