Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the basic SQL query sentences that beginners must see. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Will share 15 must-see basic SQL queries for beginners, all of which are very basic, but you may not know all of them, so take a good look.

1. Create tables and insert data into SQL

Before we start creating datasheets and inserting presentation data into tables, I'd like to explain the design philosophy of real-time datasheets, which may help you better understand SQL queries.

In database design, a very important rule is to correctly establish the relationship between primary key and foreign key.

Now let's create several data tables for restaurant order management, using a total of three data tables, Item Master table, Order Master table and Order Detail table.

Create a table:

Create the Item Master table:

CREATE TABLE [dbo]. [ItemMasters] ([Item_Code] [varchar] (20) NOT NULL, [Item_Name] [varchar] (100) NOT NULL, [Price] Int NOT NULL, [TAX1] Int NOT NULL, [Discount] Int NOT NULL, [Description] [varchar] (200) NOT NULL, [IN_DATE] [datetime] NOT NULL, [IN_USR_ID] [varchar] (20) NOT NULL, [UP_DATE] [datetime] NOT NULL [UP_USR_ID] [varchar] (20) NOT NULL, CONSTRAINT [PK_ItemMasters] PRIMARY KEY CLUSTERED ([Item_Code] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Insert data into the Item Master table:

INSERT INTO [ItemMasters] ([Item_Code], [Item_Name], [Price], [TAX1], [Discount], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE (),' SHANU', GETDATE (), 'SHANU') INSERT INTO [ItemMasters] ([Item_Code], [Item_Name], [Price] [TAX1], [Discount], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold user choice',GETDATE (),' SHANU', GETDATE (), 'SHANU') INSERT INTO [ItemMasters] ([Item_Code], [Item_Name], [Price], [TAX1], [Discount], [Description], [IN_DATE] [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE (),' SHANU', GETDATE (), 'SHANU') INSERT INTO [ItemMasters] ([Item_Code], [Item_Name], [Price], [TAX1], [Discount], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE] [UP_USR_ID]) VALUES ('Item004','Potato Fry',15,0,0,'No Comments',GETDATE (),' SHANU', GETDATE (), 'SHANU')

Create the Order Master table:

CREATE TABLE [dbo]. [OrderMasters] ([Order_No] [varchar] (20) NOT NULL, [Table_ID] [varchar] (20) NOT NULL, [Description] [varchar] (200) NOT NULL, [IN_DATE] [datetime] NOT NULL, [IN_USR_ID] [varchar] (20) NOT NULL, [UP_DATE] [datetime] NOT NULL, [UP_USR_ID] [varchar] (20) NOT NULL CONSTRAINT [PK_OrderMasters] PRIMARY KEY CLUSTERED ([Order_No] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Insert data into the Order Master table:

INSERT INTO [OrderMasters] ([Order_No], [Table_ID], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('Ord_001','T1','',GETDATE (),' SHANU', GETDATE (), 'SHANU') INSERT INTO [OrderMasters] ([Order_No], [Table_ID], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE] [UP_USR_ID]) VALUES ('Ord_002','T2','',GETDATE (),' Mak', GETDATE (), 'MAK') INSERT INTO [OrderMasters] ([Order_No], [Table_ID], [Description], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES (' Ord_003','T3','',GETDATE (), 'RAJ', GETDATE (),' RAJ')

Create the Order Detail table:

CREATE TABLE [dbo]. [OrderDetails] ([Order_Detail_No] [varchar] (20) NOT NULL, [Order_No] [varchar] (20) CONSTRAINT fk_OrderMasters FOREIGN KEY REFERENCES OrderMasters (Order_No), [Item_Code] [varchar] (20) CONSTRAINT fk_ItemMasters FOREIGN KEY REFERENCES ItemMasters (Item_Code), [Notes] [varchar] (200) NOT NULL, [QTY] INT NOT NULL, [IN_DATE] [datetime] NOT NULL [IN_USR_ID] [varchar] (20) NOT NULL, [UP_DATE] [datetime] NOT NULL, [UP_USR_ID] [varchar] (20) NOT NULL, CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ([Order_Detail_No] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]-- Now let's insert the 3 items for the above Order No 'Ord_001'.INSERT INTO [OrderDetails] ([Order_Detail_No], [Order_No], [Item_Code], [Notes], [QTY], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES (' OR_Dt_001','Ord_001','Item001','Need very Cold'] 3, GETDATE (), 'SHANU', GETDATE (),' SHANU') INSERT INTO [OrderDetails] ([Order_Detail_No], [Order_No], [Item_Code], [Notes], [QTY], [IN_DATE], [IN_USR_ID], [UP_USR_ID]) VALUES ('OR_Dt_002','Ord_001','Item004','very Hot', 2, GETDATE (), 'SHANU', GETDATE () 'SHANU') INSERT INTO [OrderDetails] ([Order_Detail_No], [Order_No], [Item_Code], [Notes], [QTY], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES (' OR_Dt_003','Ord_001','Item003','Very Spicy',4, GETDATE (), 'SHANU', GETDATE (),' SHANU')

Insert data into the Order Detail table:

INSERT INTO [OrderDetails] ([Order_Detail_No], [Order_No], [Item_Code], [Notes], [QTY], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('OR_Dt_004','Ord_002','Item002','Need very Hot',2, GETDATE (),' SHANU', GETDATE (), 'SHANU') INSERT INTO [OrderDetails] ([Order_Detail_No]) [Order_No], [Item_Code], [Notes], [QTY], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('OR_Dt_005','Ord_002','Item003','very Hot', 2, GETDATE (), 'SHANU', GETDATE (),' SHANU') INSERT INTO [OrderDetails] ([Order_Detail_No], [Order_No], [Item_Code], [Notes] [QTY], [IN_DATE], [IN_USR_ID], [UP_DATE], [UP_USR_ID]) VALUES ('OR_Dt_006','Ord_003','Item003','Very Spicy',4, GETDATE (),' SHANU', GETDATE (), 'SHANU')

2. Simple Select query statement

Select query statement is one of the most basic and important DML statements in SQL. So what is DML? The full name of DML is Data Manipulation Language (data manipulation language command), which enables users to query databases and manipulate data in existing databases.

Let's use the select statement in SQL Server to query my name (Name):

SELECT'My Name Is SYED SHANU'-- With Column Name using 'AS'SELECT' My Name Is SYED SHANU' as'MY NAME'-- With more then the one Column SELECT'My Name' as' Column1', 'Is' as' Column2', 'SYED SHANU' as' Column3'

Use the select query in the data table:

-To Display all the columns from the table we use * operator in select Statement.Select * from ItemMasters-- If we need to select only few fields from a table we can use the Column Name in Select Statement.Select Item_Code, Item_name as Item, Price, Description, In_DATE FROM ItemMasters

3. Summation and scalar function

Aggregate functions and scalar functions are built-in functions of SQL Server, and we can use them in select query statements, such as Count (), Max (), Sum (), Upper (), lower (), Round (), and so on. Let's use SQL code to explain the use of these functions:

Select * from ItemMasters-- Aggregate-- COUNT ()-> returns the Total no of records from table, AVG () returns the Average Value from Colum,MAX () Returns MaX Value from Column--, MIN () returns Min Value from Column,SUM () sum of total from ColumnSelect Count (*) TotalRows,AVG (Price) AVGPrice, MAX (Price) MAXPrice,MIN (Price) MinPrice,Sum (price) PriceTotal FROM ItemMasters-- Scalar-- UCASE ()-> Convert to Upper Case, LCASE ()-> Convert to Lower Case -- SUBSTRING ()-> Display selected char from column-> SUBSTRING (ColumnName,StartIndex,LenthofChartoDisplay)--, LEN ()-> lenth of column date,-- ROUND ()-> Which will round the valueSELECT UPPER (Item_NAME) Uppers,LOWER (Item_NAME) Lowers, SUBSTRING (Item_NAME,2,3) MidValue,LEN (Item_NAME) Lenths, SUBSTRING (Item_NAME,2,LEN (Item_NAME)) MidValuewithLenFunction, ROUND (Price,0) as Rounded FROM ItemMasters

4. Date function

Date columns are basically used in our project data tables, so the date function plays a very important role in the project. Sometimes we have to be very careful with the date function, which can cause you a lot of trouble at any time. In the project, we need to choose the appropriate date function and date format. Here are some examples of SQL date functions:

-- GETDATE ()-- > to Display the CurrentDate and Time-- Format ()-- > used to display our date in our requested formatSelect GETDATE () CurrentDateTime, FORMAT (GETDATE (), 'yyyy-MM-dd') AS DateFormats, FORMAT (GETDATE (),' HH-mm-ss') TimeFormats, CONVERT (VARCHAR (10), GETDATE (), 10) Converts1, CONVERT (VARCHAR (24), GETDATE (), 113), CONVERT (NVARCHAR, getdate (), 106) Converts2,-- here we used Convert Function REPLACE (convert (NVARCHAR, getdate (), 106) ',' /') Formats-- Here we used replace and-- convert functions. -- first we convert the date to nvarchar and then we replace the''with' / 'select * from ItemmastersSelect ITEM_NAME,IN_DATE CurrentDateTime, FORMAT (IN_DATE,'yyyy-MM-dd') AS DateFormats, FORMAT (IN_DATE,'HH-mm-ss') TimeFormats, CONVERT (VARCHAR (10), IN_DATE,10) Converts1, CONVERT (VARCHAR (24), IN_DATE,113), convert (NVARCHAR,IN_DATE, 106) Converts2,-- here we used Convert Function REPLACE (convert (NVARCHAR,IN_DATE),'' '/') Formats FROM Itemmasters

DatePart-> this function can obtain the information of year, month and day.

DateADD-> this function can add or subtract the current date.

DateDiff-> this function can compare 2 dates.

-- Datepart DATEPART (dateparttype,yourDate) SELECT DATEPART (yyyy,getdate ()) AS YEARs, DATEPART (mm,getdate ()) AS MONTHS,DATEPART (dd,getdate ()) AS Days,DATEPART (week,getdate ()) AS weeks,DATEPART (hour,getdate ()) AS hours--Days Add to add or subdtract date from a selected date.SELECT GetDate () CurrentDate,DATEADD (day,12,getdate ()) AS AddDays, DATEADD (day,-4,getdate ()) AS FourDaysBeforeDate-- DATEDIFF ()-> to display the Days between 2 dates select DATEDIFF (year) '2003-08-05 Getdate () yearDifferance, DATEDIFF (day,DATEADD (day,-24,getdate ()), getdate () daysDifferent, DATEDIFF (month,getdate (), DATEADD (Month,6,getdate () MonthDifferance

5. Other Select functions

Top-combined with the select statement, the Top function can query the first and last data records.

Order By-combined with the select statement, Order By allows query results to output data records in positive and reverse order of a field.

-- Top to Select Top first and last records using Select Statement.Select * FROM ItemMasters-- > First Display top 2 RecordsSelect TOP 2 Item_Code, Item_name as Item, Price, Description, In_DATEFROM ItemMasters-- > to Display the Last to Records we need to use the Order By Clause-- order By to display Records in assending or desending order by the columnsSelect TOP 2 Item_Code, Item_name as Item, Price, Description, In_DATEFROM ItemMastersORDER BY Item_Code DESC

The Distinct-- distinct keyword can filter duplicate data records.

Select * FROM ItemMasters--Distinct-> To avoid the Duplicate records we use the distinct in select statement-- for example in this table we can see here we have the duplicate record 'Chiken Burger'-- but with different Item_Code when i use the below select statement see what happenSelect Item_name as Item, Price, Description, IN_USR_ID FROM ItemMasters-- here we can see the Row No 3 and 5 have the duplicate record to avoid this we use the distinct Keyword in select statement.select Distinct Item_name as Item, Price, Description, IN_USR_ID FROM ItemMasters

6. Where clause

The Where clause is very important in SQL Select query statements, so why use the where clause? When do I use the where clause? The where clause uses some conditions to filter the data result set.

Let's query the data records in which Order_No is a certain value or interval from 10000 data records, and there are other conditions.

Select * from ItemMastersSelect * from OrderDetails--Where-> To display the data with certain conditions-- Now below example which will display all the records which has Item_Name='Coke'select * FROM ItemMasters WHERE ITEM_NAME='COKE'-- If we want display all the records Iten_Name which Starts with'C 'then we use Like in where clause.SELECT * FROM ItemMasters WHERE ITEM_NAME Like' C% copyright-> here we display the ItemMasters where the price will be greater then or equal to 40.muri-> to use more then one condition we can Use And or Or operator.--If we want to check the data between to date range then we can use Between Operator in Where Clause.select Item_name as Item Price, Description, IN_USR_ID FROM ItemMasters WHERE ITEM_NAME Like'C% 'AND price > = 40-> here we display the OrderDetails where the Qty will be greater 3Select * FROM OrderDetails WHERE qty > 3

Where-In clause

-- In clause-> used to display the data which is in the conditionselect * FROM ItemMasters WHERE Item_name IN ('Coffee','Chiken Burger')-- In clause with Order By-Here we display the in descending order.select * FROM ItemMasters WHERE Item_name IN (' Coffee','Chiken Burger') ORDER BY Item_Code Desc

Where-Between clause

-- between-> Now if we want to display the data between to date range then we use betweeen keywordselect * FROM ItemMastersselect * FROM ItemMasters WHERE In_Date BETWEEN '2014-09-22 15 FROM ItemMasters WHERE In_Date BETWEEN' 02.853 'AND' 2014-09-22 15:59:02.853'select * FROM ItemMasters WHERE ITEM_NAME Like'C% 'AND In_Date BETWEEN' 2014-09-22 1515 FROM ItemMasters WHERE In_Date BETWEEN '02.853' AND '2014-09-22 15DA 5902.853'

We often use the between clause to query the data of a conditional interval.

7. Group By clause

The Group By clause can group the result set of a query by a specified field:

-- Group By-> To display the data with group result.Here we can see we display all the AQggregate result by Item NameSelect ITEM_NAME,Count (*) TotalRows,AVG (Price) AVGPrice, MAX (Price) MAXPrice,MIN (Price) MinPrice,Sum (price) PriceTotal FROM ItemMasters GROUP BY ITEM_NAME-- Here this group by will combine all the same Order_No result and make the total or each order_NOSelect Order_NO,Sum (QTy) as TotalQTY FROM OrderDetails where qty > = 2 GROUP BY Order_NO-- Here the Total will be created by order_No and Item_CodeSelect Order_NO Item_Code,Sum (QTy) as TotalQTY FROM OrderDetails where qty > = 2 GROUP BY Order_NO,Item_Code Order By Order_NO Desc,Item_Code

Group By & Having clause

Group By Clause-- here this will display all the Order_no Select Order_NO,Sum (QTy) as TotalQTY FROM OrderDetails GROUP BY Order_NO-- Having Clause-- This will avoid the the sum (qty) less then 4 Select Order_NO,Sum (QTy) as TotalQTY FROM OrderDetails GROUP BY Order_NO HAVING Sum (QTy) > 4

8. Subquery

Subqueries generally appear in join queries and nested queries within where, and can be used in select, update, and delete statements.

-- Sub Query-- Here we used the Subquery in where clause to get all the Item_Code where the price > 40 now this sub-- query reslut we used in our main query to filter all the records which Item_code from Subquery resultSELECT * FROM ItemMasters WHERE Item_Code IN (SELECT Item_Code FROM ItemMasters WHERE price > 40)-- Sub Query with Insert StatementINSERT INTO ItemMasters ([Item_Code], [Item_Name], [Price], [TAX1], [Discount], [Description], [IN_DATE], [IN_USR_ID] [UP_DATE], [UP_USR_ID]) Select 'Item006', Item_Name,Price+4,TAX1,Discount,Description, GetDate (),' SHANU',GetDate (), 'SHANU' from ItemMasters where Item_code='Item002'-- After insert we can see the result as Select * from ItemMasters

9. Join query

So far we have been exposed to a lot of single-table queries, and now let's use join queries to get data from multiple tables.

A simple join statement:

-- Now we have used the simple join with out any condition this will display all the-- records with duplicate data to avaoid this we see our next example with conditionSELECT * FROM Ordermasters,OrderDetails-- Simple Join with Condition now here we can see the duplicate records now has been avoided by using the where checing with both table primaryKey fieldSELECT * FROM Ordermasters as M, OrderDetails as D where M.Order_NO=D.Order_NO and M.Orderlies NOAA orbits 001mm Now to make more better understanding we need to select the need fields from both-- table insted of displaying all column.SELECT M.order_NO M.Tablehammer ID Department D.Orderbook detailing Notebook Notebook FROM Ordermasters as M, OrderDetails as D where M.Order_NO=D.Order_NO-- Now lets Join 3 table SELECT M.ordergarden Notebook Notebook ID Magistrate D.Orderbook detaileddescription Novoli I.Itemized NameMagery D.NotesMagne D.QtyMagne I.Price, I.Price*D.Qty as TotalPrice FROM Ordermasters as M, OrderDetails as D ItemMasters as I where M.Order_NO=D.Order_NO AND D.Item_Code=I.Item_Code

Inner Join,Left Outer Join,Right Outer Join and Full outer Join

Here are the various types of connection query codes:

-- INNER JOIN-- This will display the records which in both table Satisfy here i have used Like in where class which display the SELECT M.ordergarden NODORIN M.Tablehammer IDMagazine D.Orderlies detailwriting Noreparting I.Itemized NamereageD.NotesNotesJournal D.QtyMagi I.Price I.Price*D.Qty as TotalPrice FROM Ordermasters as M Inner JOIN OrderDetails as D ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I ON D.Item_Code=I.Item_Code WHERE M.Table_ID like 'T%'--LEFT OUTER JOIN-- This will display the records which Left side table Satisfy SELECT M.ordergarden NODORING M.Tablehammer IDMagazine D.Orderlies detailwriting Norelledge I.Itemized NameMemagrary D.NotesMagic D.QtyMagi I.Price I.Price*D.Qty as TotalPrice FROM Ordermasters as M LEFT OUTER JOIN OrderDetails as D ON M.Order_NO=D.Order_NO LEFT OUTER JOIN ItemMasters as I ON D.Item_Code=I.Item_Code WHERE M.Table_ID like 'T%'--RIGHT OUTER JOIN-- This will display the records which Left side table Satisfy SELECT M.ordergarden NODORING M.Tablehammer IDMagazine D.Orderlies detailwriting Norelledge I.Itemized NameMemagrary D.NotesMagic D.QtyMagi I.Price I.Price*D.Qty as TotalPrice FROM Ordermasters as M RIGHT OUTER JOIN OrderDetails as D ON M.Order_NO=D.Order_NO RIGHT OUTER JOIN ItemMasters as I ON D.Item_Code=I.Item_Code WHERE M.Table_ID like 'T%'--FULL OUTER JOIN-- This will display the records which Left side table Satisfy SELECT M.ordergarden NODORING M.Tablehammer IDMagazine D.Orderlies detailwriting Norelledge I.Itemized NameMemagrary D.NotesMagic D.QtyMagi I.Price I.Price*D.Qty as TotalPrice FROM Ordermasters as M FULL OUTER JOIN OrderDetails as D ON M.Order_NO=D.Order_NO FULL OUTER JOIN ItemMasters as I ON D.Item_Code=I.Item_Code WHERE M.Table_ID like'T%'

10. Union merge query

Union query can merge the data of multiple tables, Union will only query the unique data, and Union ALL will query the duplicate data.

Select column1,Colum2 from Table1UnionSelect Column1,Column2 from Table2Select column1,Colum2 from Table1Union AllSelect Column1,Column2 from Table2

Specific examples are as follows:

-- Select with different where condition which display the result as 2 Table resultselect Item_Code,Item_Name,Price,Description FROM ItemMasters where price 44 murals-Union with same table but with different where condition now which result as one table which combine both the result.select Item_Code,Item_Name,Price,Description FROM ItemMasters where price 44 murals-Union ALL with Join sample SELECT M.orderweights NOgraine M.Tablehammer ID repertory D.Orderlies detailwriting Noreline I.Itemology Namememery D.NotesMagic D.QtyJere I.Price I.Price*D.Qty as TotalPrice FROM Ordermasters as M (NOLOCK) Inner JOIN OrderDetails as D ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I ON D.Item_Code=I.Item_Code WHERE I.Price 44

11. Common table expression (CTE)-- With statement

CTE can be thought of as a temporary result set that can be referenced multiple times in the next SELECT,INSERT,UPDATE,DELETE,MERGE statement. Using common expressions can make statements clearer and more concise.

Declare @ sDate datetime, @ eDate datetime;select @ sDate = getdate ()-5, @ eDate = getdate () + 16th Murray select @ sDate StartDate,@eDate EndDate With cte as (select @ sDate StartDate,'W'+convert (varchar (2), DATEPART (wk, @ sDate)) +'('+ convert (varchar (2), @ sDate,106) +')'as' SDT' union all select dateadd (DAY, 1, StartDate), 'W'+convert (varchar (2), DATEPART (wk, StartDate)) +' ('+ convert (varchar (2), dateadd (DAY, 1, StartDate)) +')'as' SDT' FROM cte WHERE dateadd (DAY, 1, StartDate) 40

13. Pivot row transfer column

Pivot can help you convert data rows into data columns, as follows:

-- Simple Pivot Example SELECT * FROM ItemMasters PIVOT (SUM (Price) FOR ITEM_NAME IN ([Chiken Burger], Coffee,Coke)) AS PVTTable-- Pivot with detail exampleSELECT * FROM (SELECT ITEM_NAME, price as TotAmount FROM ItemMasters) as sPIVOT (SUM (TotAmount) FOR [ITEM_NAME] IN ([Chiken Burger], [Coffee], [Coke]) AS MyPivot

14. Stored procedure

I often see people asking how to write SQL statements for multiple queries in SQL Server and then use them in C # programs. Stored procedures can do this. Stored procedures can aggregate multiple SQL queries together. The basic structure of creating stored procedures is as follows:

CREATE PROCEDURE [ProcedureName] AS BEGIN-- Select or Update or Insert query.ENDTo execute SP we useexec ProcedureName

Create a stored procedure with no parameters:

-Author: Shanu-- Create date: 2014-09-15-- Description: To Display Pivot Data-- Latest-- Modifier: Shanu -- Modify date: 2014-09-15-- =-- exec USP_SelectPivot-- = Create PROCEDURE [dbo]. [USP_SelectPivot] AS BEGIN DECLARE @ MyColumns AS NVARCHAR (MAX) @ SQLquery AS NVARCHAR (MAX)-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot queryselect @ MyColumns = STUFF ((SELECT','+ QUOTENAME (Item_NAME) FROM ItemMasters GROUP BY Item_NAME ORDER BY Item_NAME FOR XML PATH (''), TYPE). Value ('.', 'NVARCHAR (MAX)'), 1 '')-- here we use the above all Item name to disoplay its price as column and row displayset @ SQLquery = N'SELECT'+ @ MyColumns + N' from (SELECT ITEM_NAME, price as TotAmount FROM ItemMasters) x pivot (SUM (TotAmount) for ITEM_NAME in ('+ @ MyColumns + N')) p 'exec sp_executesql @ SQLquery RETURN END

15. Function Function

We introduced the most basic SQL functions such as MAX (), SUM (), GetDate (), and so on. Now let's look at how to create a custom SQL function. The format of the creation function is as follows:

Create Function functionNameAsBeginEND

Here is an example of a simple function:

Alter FUNCTION [dbo]. [ufnSelectitemMaster] () RETURNS int AS-- Returns total Row count of Item Master.BEGIN DECLARE @ RowsCount AS int;Select @ RowsCount= count (*) + 1 from ItemMasters RETURN @ RowsCount;END-- to View Function we use select and fucntion Nameselect [dbo]. [ufnSelectitemMaster] ()

The following function enables you to get the last day of the current month from a given date:

ALTER FUNCTION [dbo]. [ufn_LastDayOfMonth] (@ DATE NVARCHAR (10)) RETURNS NVARCHAR (10) ASBEGIN RETURN CONVERT (NVARCHAR (10), DATEADD (D,-1, DATEADD (M, 1, CAST (SUBSTRING (@ DATE,1,7) +'- 01' AS DATETIME)), ENDSELECT dbo.ufn_LastDayOfMonth ('2014-09-01') AS LastDay these are the basic SQL queries that the editor shares with you, and if you happen to have similar doubts It may be understood with reference to the above analysis. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.