SQL Review

  1. Row_number()

https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

Syntax

ROW_NUMBER ()

OVER ([PARTITION BY value_exp, … [ n ]] order_by_clause)

OVER – Specify the order of the rows.

ORDER BY – Provide sort order for the records.

 

 

SELECT *, ROW_NUMBER() OVER (ORDER BY StatusID) AS rownumber

FROM dbo.Claim

 

SELECT *, ROW_NUMBER() OVER (PARTITION BY StatusID ORDER BY ClaimID) AS rownumber

 

FROM dbo.Claim

2. OVER with Aggregate

USE AdventureWorks2012; 

GO 

SELECT SalesOrderID, ProductID, OrderQty 

    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total 

    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Avg” 

    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Count” 

    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Min” 

    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS “Max” 

FROM Sales.SalesOrderDetail  

WHERE SalesOrderID IN(43659,43664); 

GO  

3. CTE:

WITH quota_cte (BusinessEntityID, QuotaDate, SalesQuota, QuotaVersion)

AS (

   SELECT BusinessEntityID, QuotaDate, SalesQuota,

   ROW_NUMBER() OVER (

      PARTITION BY BusinessEntityID

      ORDER BY QuotaDate) AS QuotaVersion

   FROM Sales.SalesPersonQuotaHistory)

4. Recursive CTE:

WITH cte_name (column1, column2, …)

AS

(

   cte_query_definition — Anchor member

   UNION ALL

   cte_query_definition — Recursive member; references cte_name.

)

— Statement using the CTE

SELECT *

FROM   cte_name

5. Multiple CTEs:

WITH one (x)

AS (SELECT 1 AS x),

 

     two (y)

AS (SELECT x+1 AS y FROM one),

 

     three (z)

AS (SELECT y+1 AS z FROM two)

 

SELECT one.x, two.y, three.z

FROM one

CROSS JOIN two

CROSS JOIN three;

6. Cast_Convert:

— CAST Syntax: 

CAST ( expression AS data_type [ ( length ) ] ) 

 

— CONVERT Syntax: 

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

 

SELECT 9.5 AS Original, CAST(9.5 AS int) AS int,

    CAST(9.5 AS decimal(6,4)) AS decimal;

 

SELECT 9.5 AS Original, CONVERT(int, 9.5) AS int,

    CONVERT(decimal(6,4), 9.5) AS decimal;

7.Parse

PARSE ( string_value AS data_type [ USING culture ] ) 

SELECT PARSE(‘Monday, 13 December 2010’ AS datetime2 USING ‘en-US’) AS Result; 

8. LAG_LEAD :

LAG (scalar_expression [,offset] [,default]) 

             OVER ( [ partition_by_clause ] order_by_clause ) 

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,  

       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota 

FROM Sales.SalesPersonQuotaHistory 

 

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN (‘2005’,‘2006’);

9.DATENAME

SELECT DATENAME(year, GETDATE()) as Year,

       DATENAME(week, GETDATE()) as Week,

       DATENAME(dayofyear, GETDATE()) as DayOfYear,

       DATENAME(month, GETDATE()) as Month,

       DATENAME(day, GETDATE()) as Day,

 

       DATENAME(weekday, GETDATE()) as WEEKDAY

10. GroupBy Weekly

SELECT   DATENAME(year, OrderDate) as OrderYear,

         DATENAME(week, OrderDate) as OrderWeek,

         SUM(TotalDue) as WeeklySales

FROM     Sales.SalesOrderHeader

WHERE    DATENAME(year, OrderDate) = ‘2012’

GROUP BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)

ORDER BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)

 

 

#19. DATEPART

SELECT DATEPART(YEAR, GETDATE()) as Year,

       DATEPART(WEEK, GETDATE()) as Week,

       DATEPART(DAYOFYEAR, GETDATE()) as DayOfYear,

       DATEPART(MONTH, GETDATE()) as Month,

       DATEPART(DAY, GETDATE()) as Day,

       DATEPART(WEEKDAY, GETDATE()) as WEEKDAY

 

#20. DATEPART: sorting correctly

   SELECT   DATEPART(year, OrderDate) as OrderYear,

             DATEPART(week, OrderDate) as OrderWeek,

             SUM(TotalDue) as WeeklySales

             FROM     Sales.SalesOrderHeader

             WHERE    DATEPART(year, OrderDate) = 2014

             GROUP BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)

             ORDER BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)

 

#21. DAY, MONTH, YEAR:

 

SELECT HireDate,

                       YEAR(HireDate) as HireYear,

                       MONTH(HireDate) HireMonth,

                       DAY(HireDate) as HireDay

             FROM   HumanResources.Employee

#22. DATEDIFF

DATEDIFF(date part, start value, end value)

               SELECT   NationalIDNumber,

                            HireDate,

                            DATEDIFF(year, HireDate, GETDATE()) YearsOfService

             FROM     HumanResources.Employee

             WHERE    DATEDIFF(year, HireDate, GETDATE()) >= 10

             ORDER BY YearsOfService DESC

 

#23. DATEADD

DATEADD(date part, number, value)

Select DATEADD(day, 2, ‘01/11/2019’) è result: 01/13/2019

12. CHOOSE

USE AdventureWorks2012; 

GO 

SELECT ProductCategoryID, CHOOSE (ProductCategoryID, ‘A’,’B’,’C’,’D’,’E’) AS Expression1 

FROM Production.ProductCategory; 

 

Example 2: 

USE AdventureWorks2014; 

GO 

SELECT JobTitle, HireDate, CHOOSE(MONTH(HireDate),‘Winter’,‘Winter’, ‘Spring’,‘Spring’,‘Spring’,‘Summer’,‘Summer’,  

                                                  ‘Summer’,‘Autumn’,‘Autumn’,‘Autumn’,‘Winter’) AS Quarter_Hired 

FROM HumanResources.Employee 

WHERE  YEAR(HireDate) > 2005 

 

ORDER BY YEAR(HireDate);  

 

13. CASE WHEN AND IIF

EXAMPLE 1:

SELECT CASE 1

WHEN 1 THEN ‘PRINT SMT’

ELSE ‘PRINT NOTHING’

END

EXAMPLE 2:

DECLARE @a int = 45, @b int = 40; 

 

SELECT IIF ( @a > @b, ‘TRUE’, ‘FALSE’ ) AS Result;  

 

14. NTILE, RANK, RANKDENSE

https://www.mssqltips.com/sqlservertip/2905/forgotten-sql-server-functions–varp-soundex-and-ntile/

 

SELECT

ROW_NUMBER() OVER(ORDER by Employee_City ASC) AS ROWNUM_CITYWISE,

ROW_NUMBER() OVER(PARTITION BY Employee_City ORDER by Employee_City ASC) AS ROWNUM_PART_CITYWISE,

RANK() OVER(ORDER BY Employee_City ASC) AS RANK_CITYWISE,

DENSE_RANK() OVER(ORDER BY Employee_City ASC) AS DENSRANK_CITYWISE,

NTILE(4) OVER(ORDER BY EMPLOYEE_CITY ASC) AS NTILE_CITYWISE,

Employee_Id,

Employee_Name,

Employee_Address,

Employee_City,

Employee_State,

Employee_Salary

 

FROM Employee

 

15.PIVOT

Example1:

— Pivot table with one row and five columns 

SELECT ‘AverageCost’ AS Cost_Sorted_By_Production_Days,  

[0], [1], [2], [3], [4] 

FROM 

(SELECT DaysToManufacture, StandardCost  

    FROM Production.Product) AS SourceTable 

PIVOT 

( 

AVG(StandardCost) 

FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) 

) AS PivotTable;

 

 

 

Example2:

SELECT  VendorID,

             [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5 

FROM (

       SELECT EmployeeID,VendorID , PurchaseOrderID

       FROM Purchasing.PurchaseOrderHeader

       ) AS dt

PIVOT

(

       count(PurchaseOrderID)

       FOR EmployeeID IN ( [250], [251], [256], [257], [260] ) 

) AS pvt

 

ORDER BY pvt.vendorid

 

16. Unpivot

— Create the table and insert values as portrayed in the previous example. 

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, 

    Emp3 int, Emp4 int, Emp5 int); 

GO 

INSERT INTO pvt VALUES (1,4,3,5,4,4); 

INSERT INTO pvt VALUES (2,4,1,5,5,5); 

INSERT INTO pvt VALUES (3,4,3,5,4,4); 

INSERT INTO pvt VALUES (4,4,2,5,5,4); 

INSERT INTO pvt VALUES (5,5,1,5,5,5); 

GO 

— Unpivot the table. 

SELECT VendorID, Employee, Orders 

FROM  

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 

   FROM pvt)

UNPIVOT 

   (Orders FOR Employee IN  

      (Emp1, Emp2, Emp3, Emp4, Emp5) 

)AS unpvt; 

GO 

 

 

17. Pivot with DenseRank

DECLARE @Pet Table

(

  PetID INT

, OwnerID INT –FK to fictitious PetOwner table

, PetName NVARCHAR(50)

, AnimalType NVARCHAR(10)

)

DECLARE @PetDetail Table

(

  PetDetailID INT

, PetID INT — FK to @Pet table

, DetailType NVARCHAR(50)

, DetailValue NVARCHAR(50)

)

INSERT INTO @Pet(PetID, OwnerID, PetName, AnimalType) VALUES

  (1, 1, ‘Sonya’, ‘Dog’)

, (2, 2, ‘Bessy’, ‘Cat’)

, (3, 2, ‘Speedy’, ‘Cat’)

, (4, 3, ‘Nema’, ‘Cat’)

, (5, 3, ‘Milo’, ‘Cat’)

, (6, 3, ‘Naala’, ‘Cat’)

, (7, 3, ‘Trung’, ‘Cat’)

INSERT INTO @PetDetail(PetDetailID, PetID, DetailType, DetailValue) VALUES

  (1, 1, ‘Breed’, ‘Norwegian Samoyed’)

, (2, 1, ‘Gender’, ‘F’)

, (3, 2, ‘Breed’, ‘Holstein Hybrid’)

, (4, 2, ‘Gender’, ‘F’)

, (5, 3, ‘Breed’, ‘DSH’)

, (6, 3, ‘Gender’, ‘F’)

, (7, 4, ‘Breed’, ‘Manx’)

, (8, 4, ‘Gender’, ‘M’)

, (9, 5, ‘Breed’, ‘DSH’)

, (10, 5, ‘Gender’, ‘F’)

, (11, 6, ‘Breed’, ‘Lynx-Point Siamese’)

, (12, 6, ‘Gender’, ‘F’)

 

 

SELECT

OwnerID

, AnimalType

, Pet1Name = MAX([PetName1])

, Pet1Gender = MAX([Gender1])

, Pet1Breed = MAX([Breed1])

, Pet2Name = MAX([PetName2])

, Pet2Gender = MAX([Gender2])

, Pet2Breed = MAX([Breed2])

, Pet3Name = MAX([PetName3])

, Pet3Gender = MAX([Gender3])

, Pet3Breed = MAX([Breed3])

FROM (

SELECT

      p.OwnerID

      , p.PetName

      , p.AnimalType

      , pd.DetailType

      , pd.DetailValue

      ,pd.DetailType + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS NVARCHAR) AS [PetNumber]

      ,‘PetName’ + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS NVARCHAR) AS [PetNamePivot]

FROM @Pet p

INNER JOIN @PetDetail pd ON p.PetID = pd.PetID

) AS query

PIVOT (MAX(DetailValue)

      FOR PetNumber IN ([Gender1],[Gender2],[Gender3], [Breed1], [Breed2], [Breed3])) AS Pivot1

PIVOT (MAX(PetName)

      FOR PetNamePivot IN ([PetName1],[PetName2],[PetName3])) AS Pivot2

GROUP BY

  OwnerID

, AnimalType

 

ORDER BY OwnerID

 

18. Dynamic Pivot with Group By DATE

SELECT *

INTO #temptable

FROM (

SELECT

             CASE WHEN DATEPART(YEAR, OrderDate) IS NULL THEN ‘Total’

             ELSE CAST(DATEPART(YEAR, orderdate) AS NVARCHAR(10)) END yr,

             CASE

                           WHEN GROUPING_ID(DATEPART(YEAR, OrderDate),DATEPART(MONTH, orderdate)) = 3 THEN ‘Total All’

                           WHEN GROUPING_ID(DATEPART(YEAR, OrderDate),DATEPART(MONTH, orderdate)) = 1 THEN ‘Total ‘ + CAST(DATEPART(YEAR, OrderDate) AS NVARCHAR(10))

                           WHEN LEN(DATEPART(MONTH, OrderDate) )=THEN ‘0’ + CAST(DATEPART(Month, OrderDate) AS NVARCHAR(10))

                           ELSE CAST(DATEPART(MONTH, OrderDate) AS NVARCHAR(10)) END mon,

                 SUM(TotalDue) AS TotalDue FROM Sales.SalesOrderHeader

GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, orderdate) WITH cube

) AS TABLEx

 

DECLARE @moncol NVARCHAR(max), @sql NVARCHAR(max)

 SET @moncol = ;

 SELECT @moncol = COALESCE(@moncol + ‘, ‘,) + QUOTENAME(mon)

FROM (

       SELECT DISTINCT mon FROM #temptable

) AS x4;

 SET @sql =

‘SELECT yr, ‘ + STUFF(@moncol, 1, 2, ) +

‘ FROM

(SELECT * FROM #temptable) AS datatable

PIVOT

(

       SUM(TotalDue)

       FOR mon IN   (‘ + STUFF(@moncol, 1, 2, ) + ‘)

) AS pvtable;’;

 

 PRINT @sql

EXEC sys.sp_executesql @sql;

 DROP TABLE #temptable;

19. Dynamic pivot

DECLARE @Pet Table

(

  PetID INT

, OwnerID INT –FK to fictitious PetOwner table

, PetName NVARCHAR(50)

, AnimalType NVARCHAR(10)

)

DECLARE @PetDetail Table

(

  PetDetailID INT

, PetID INT — FK to @Pet table

, DetailType NVARCHAR(50)

, DetailValue NVARCHAR(50)

)

INSERT INTO @Pet(PetID, OwnerID, PetName, AnimalType) VALUES

  (1, 1, ‘Sonya’, ‘Dog’)

, (2, 2, ‘Bessy’, ‘Cat’)

, (3, 2, ‘Speedy’, ‘Cat’)

, (4, 3, ‘Nema’, ‘Cat’)

, (5, 3, ‘Milo’, ‘Cat’)

, (6, 3, ‘Naala’, ‘Cat’)

, (7, 3, ‘Trung’, ‘Cat’)

INSERT INTO @PetDetail(PetDetailID, PetID, DetailType, DetailValue) VALUES

  (1, 1, ‘Breed’, ‘Norwegian Samoyed’)

, (2, 1, ‘Gender’, ‘F’)

, (3, 2, ‘Breed’, ‘Holstein Hybrid’)

, (4, 2, ‘Gender’, ‘F’)

, (5, 3, ‘Breed’, ‘DSH’)

, (6, 3, ‘Gender’, ‘F’)

, (7, 4, ‘Breed’, ‘Manx’)

, (8, 4, ‘Gender’, ‘M’)

, (9, 5, ‘Breed’, ‘DSH’)

, (10, 5, ‘Gender’, ‘F’)

, (11, 6, ‘Breed’, ‘Lynx-Point Siamese’)

, (12, 6, ‘Gender’, ‘F’)

 

–SELECT * FROM @Pet

 

–SELECT * FROM @PetDetail

 

 

DECLARE @petnamepv NVARCHAR(max), @petdetailpv NVARCHAR(MAX), @sql NVARCHAR(max), @petnamepv1 NVARCHAR(max), @petdetailpv1 NVARCHAR(MAX);

 

SET @petdetailpv = ”;

SET @petnamepv = ”;

SET @petdetailpv1 = ”;

SET @petnamepv1 = ”;

 

WITH cte_temptable(ownerid, animaltype, petname, detailtype, detailvalue, petnamecol, detailcol) AS 

(

SELECT p.OwnerID, p.AnimalType, p.PetName, pd.DetailType, pd.DetailValue,

‘petname’ + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName asc) AS NVARCHAR) AS [petnamecol],

pd.DetailType + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC ) AS NVARCHAR) AS [detailcol] 

FROM 

@Pet p

INNER JOIN

     @PetDetail pd ON pd.PetID = p.PetID

)

 

–Create once time

SELECT ownerid, animaltype, petname, detailtype, detailvalue, petnamecol, detailcol

INTO #temptable FROM cte_temptable

 

 

SELECT @petdetailpv1 = COALESCE(@petdetailpv1 + ‘, ‘,”) + ‘MAX(‘ + QUOTENAME(detailcol) + ‘) AS ‘ + detailcol,

@petdetailpv = COALESCE(@petdetailpv + ‘, ‘,”) + QUOTENAME(detailcol)

FROM (

SELECT DISTINCT detailcol FROM #temptable

) AS Y1

 

SELECT @petnamepv1 = COALESCE(@petnamepv1 + ‘, ‘,”) + ‘MAX(‘ + QUOTENAME(petnamecol) + ‘) AS ‘ + petnamecol,

@petnamepv = COALESCE(@petnamepv + ‘, ‘,”) + QUOTENAME(petnamecol)

FROM (

SELECT DISTINCT petnamecol FROM #temptable

) AS X1

 

 

SET @sql = 

 

‘SELECT ownerid, animaltype, ‘ + STUFF(@petnamepv1, 1, 2, ”) + ‘,’ + STUFF(@petdetailpv1, 1, 2, ”) +

‘ FROM (

SELECT ownerid, animaltype, petname, detailtype, detailvalue, petnamecol, detailcol

FROM #temptable

) AS dt

PIVOT

(

MAX(petname)

FOR petnamecol IN (‘ + STUFF(@petnamepv, 1, 2, ”) + ‘)

) AS pv1

PIVOT 

(

MAX(detailvalue)

FOR detailcol IN (‘ + STUFF(@petdetailpv, 1, 2, ”) + ‘)

) AS pv2

Group By ownerid, animaltype

order by ownerid;

‘;

 

PRINT @sql

EXEC sys.sp_executesql @sql

 

DROP TABLE #temptable

 

 

20. GROUPING  (FILL IN NULL WITH “TOTAL” OR “ALL”) return 0 or 1 —- 1 mean there is grouping on the row.

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)

INSERT Sales VALUES(1, 2005, 12000)

INSERT Sales VALUES(1, 2006, 18000)

INSERT Sales VALUES(1, 2007, 25000)

INSERT Sales VALUES(2, 2005, 15000)

INSERT Sales VALUES(2, 2006, 6000)

INSERT Sales VALUES(3, 2006, 20000)

INSERT Sales VALUES(3, 2007, 24000)

 

SELECT

      CASE WHEN GROUPING(Yr) = 0

            THEN CAST (Yr AS CHAR(4))

            ELSE ‘ALL’

      END AS Yr,

      SUM(Sales) AS Sales

FROM Sales

 

GROUP BY Yr WITH ROLLUP

21. GROUPBY WITH ROLLUP

Example:

SELECT * FROM #temp

UNION ALL

SELECT period, name, sum(marks)

FROM #temp

GROUP BY period, name WITH ROLLUP

 

HAVING period IS NOT NULL AND name IS NULL

22. GROUPBY WITH Grouping Sets – pivot dynamic query

 SELECT

       CASE WHEN country IS NULL THEN ‘z(Total)’

                    ELSE country END country,

                    CASE WHEN state IS NULL THEN ‘z(Total)’

                    ELSE state END state,

                           CASE WHEN city IS NULL THEN ‘z(Total)’

                    ELSE city END city,       

                    SUM(population) AS pol

INTO #temptable

FROM tblPopulation

GROUP BY GROUPING SETS

(

       (country,state, city),

       (country, state),

       (country),

       (city),

       (city, country),

       ()

)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = COALESCE(@cols + ‘, ‘,) + QUOTENAME(city)

FROM (

             SELECT DISTINCT city FROM #temptable

) AS x

 SET @sql =

‘SELECT *

FROM (

       SELECT * FROM #temptable

) AS datatable

 PIVOT

(

       SUM(pol)

       FOR city IN (‘ + @cols + ‘)

) as pv;’;

  PRINT @sql;

 EXEC sys.sp_executesql @sql

 DROP TABLE #temptable

 

 

23. GROUPBY WITH Grouping Sets – pivot dynamic query for DATE

SELECT *

INTO #temptable

FROM

(

       SELECT

                    CASE WHEN CAST(DATEPART(YEAR,OrderDate) AS NVARCHAR) IS NULL THEN ‘Total’

                    ELSE CAST(DATEPART(YEAR,OrderDate) AS NVARCHAR) END DT_Year,

                    CASE WHEN CAST(DATEPART(MONTH,OrderDate) AS NVARCHAR) IS NULL THEN ‘Total’

                    ELSE CAST(DATEPART(MONTH,OrderDate) AS NVARCHAR) END DT_Month,

                    SUM(TotalDue) AS TotalDue

       FROM Sales.SalesOrderHeader

       GROUP BY GROUPING SETS

       (

             (DATEPART(YEAR,OrderDate),DATEPART(month,OrderDate)),

             (DATEPART(YEAR,OrderDate)),

             (DATEPART(month,OrderDate)),

             ()

       )

) AS x

 

DECLARE @monthcol NVARCHAR(max), @sql NVARCHAR(max);

SELECT @monthcol = COALESCE(@monthcol + ‘, ‘,) + QUOTENAME(DT_Month)

FROM (SELECT DISTINCT TOP 100 DT_Month FROM #temptable ORDER BY DT_Month) AS x1;

 SET @sql =

‘SELECT * FROM (

       SELECT * from #temptable

       ) AS datatable

PIVOT

(

       SUM(TotalDue)

       FOR DT_Month IN (‘ + @monthcol + ‘)

) as pv

order by DT_Year

;’;

 PRINT @sql;

 EXEC sys.sp_executesql @sql;

 DROP TABLE #temptable

 

 

24. GROUPING_ID

+ USING GROUPING TO MARK SUB-TOTAL(1) OR TOTAL(3)  (BINARY OF 11 IS 3)

+ NO NEED TO CAST

USE company

SELECT 

department AS Department,

gender AS Gender,

sum(salary) as Salary_Sum,

CAST(GROUPING(department) AS VARCHAR(1)) +

CAST(GROUPING(gender) AS VARCHAR (1))  as Grouping,

GROUPING_ID(department, gender) as Grouping_Id

FROM employee

 

GROUP BY ROLLUP (department, gender)

 

 

25. GROUPBY

#Group By:

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB; 

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB; 

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB; 

 

SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;

 

 

26. CASE WHEN:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    WHEN conditionN THEN resultN

    ELSE result

END;

27. #Read through XML file each record

 DECLARE @xml xml

SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML)

   Iterate through each of the “users\user” records in our XML

SELECT

    x.Rec.query(‘./firstName’).value(‘.’, ‘nvarchar(2000)’) AS ‘FirstName’,

    x.Rec.query(‘./lastName’).value(‘.’, ‘nvarchar(2000)’) AS ‘LastName’,

    x.Rec.query(‘./age’).value(‘.’, ‘int’) AS ‘Age’

FROM @xml.nodes(‘/users/user’) as x(Rec)

28. Import XML file into DB, then read the file:

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

 Step1: CREATE TABLE

CREATE TABLE XMLwithOpenXML

(

Id INT IDENTITY PRIMARY KEY,

XMLData XML,

LoadedDateTime DATETIME

)

 STEP2: INSERT FILE INTO TABLE

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

FROM OPENROWSET(BULK ‘C:\Users\troyd\OneDrive\Documents\Per\Resume\Interview Prep\catalog.xml’, SINGLE_BLOB) AS x;

–FROM OPENROWSET(BULK ‘D:\OpenXMLTesting.xml’, SINGLE_BLOB) AS x;

 STEP3: READ DATA AND PARSE IT INTO DATA TABLE

 DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

 SELECT @XML = XMLData FROM XMLwithOpenXML

 EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

 SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity

FROM OPENXML(@hDoc, ‘ROOT/Customers/Customer/Orders/Order/OrderDetail’)

WITH

(

CustomerID [varchar](50) ‘../../../@CustomerID’,

CustomerName [varchar](100) ‘../../../@CustomerName’,

Address [varchar](100) ‘../../../@Address’,   —WHY ADDRESS HAS 3?

OrderID [varchar](1000) ‘../@OrderID’,

OrderDate datetime ‘../@OrderDate’,

ProductID [varchar](50) ‘@ProductID’,

Quantity int ‘@Quantity’

)

 EXEC sp_xml_removedocument @hDoc

 

GO

29. OPENXML Flag

attribute names (a flag value of 1), the element names (a flag value of 2) or both (a flag value of 3)

EXEC sys.sp_xml_preparedocument @Handle OUTPUT , @Xml; –Prepare a parsed document

 SELECT *

FROM

       OPENXML(@Handle, ‘/CustomerOrders/Customer’, 3) –test it with 1,2,3

           WITH (   custid NVARCHAR(10) ,

                companyname NVARCHAR(40) ,

                [Order] NVARCHAR(20)

                );

 EXEC sys.sp_xml_removedocument @Handle; –Remove the handle

 

GO

30. FOR XML PATH , FOR XML AUTO to export data to xml

 USE AdventureWorks2014

GO

SELECT

    TOP 3

       BusinessEntityID AS ‘Persons.PersonID’,

       FirstName AS ‘Persons.FirstName’,

       MiddleName AS ‘Persons.MiddleName’,

       LastName AS ‘Persons.LastName’

FROM

       [Person].[Person]

 

FOR XML PATH

31. STORE PROCEDURE

———-SP with OUTPUT

CREATE PROCEDURE usp_GetAddressCount @city NVARCHAR(30), @AddressCount INT OUTPUT

AS SELECT @AddressCount = COUNT(*) FROM Person.Address

WHERE City = @city

 ——–EXECUTE SP WITH OUTPUT

DECLARE @AddressCount int

EXEC dbo.usp_GetAddressCount @City = ‘Calgary’, @AddressCount = @AddressCount OUTPUT

SELECT @AddressCount

——-SP WITH TRY CATCH

CREATE PROCEDURE usp_TestTryCatch

AS

BEGIN TRY

       SELECT 1/0

END TRY

BEGIN CATCH

       SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

 EXEC usp_TestTryCatch

 32. FUNCTION

——-SCALAR FUNCTION————-

 CREATE FUNCTION subtractnum (@num2 int)

RETURNS int

AS

BEGIN

DECLARE @result INT

       SET @result = 10 @num2

RETURN @result

END

 SELECT dbo.subtractnum(1)

 —–Inline Table Function—————-

 CREATE FUNCTION fn_GetPopulationByCountry(@countryname nvarchar(20))

RETURNS TABLE

AS

RETURN SELECT * FROM tblPopulation WHERE Country = @countryname

 SELECT * FROM dbo.fn_GetPopulationByCountry(‘vietnam’)

 ——Multi-Statement table Valued Function—–

 CREATE FUNCTION fn_MultiGetPopulationByCountry(@countryname nvarchar(20))

RETURNS @table TABLE(state nvarchar(30), city nvarchar(50), population decimal)

AS

BEGIN

       INSERT INTO @table

       SELECT state, city, Population FROM tblPopulation WHERE country=@countryname

       Return

END

 SELECT * FROM fn_MultiGetPopulationByCountry(‘vietnam’)

Leave a Reply

Close Menu