Roll Up Multiple Rows into a single rows and column
By: Ashutosh Dixit Date: 01 May 2013
By: Ashutosh Dixit Date: 25 April 2013
Sql Server Basic - Contents
Day 1- Introduction to MS SQL Server
Day 2- SQL Server installation
Day 3- What is RDBMS?
Day 4- Normalization
Day 5- De-Normalization
Day 6- ACID Property
Day 7- Variables and Data Types
Day 8- Writing basic queries
Day 9- Writing Advance Queries
Day 10- SQL Server Constraints
Day 11- Aggregate Functions
Day 12- Sub Queries
Day 13- Joins
Day 14- Indexes
Day 15- Views
Day 16- Stored Procedures
Day 17- Triggers
Day 18- User Defined functions
Day 19- Linked Server
Day 20- Cursor
Day 21- Collation
Day 22- Transactions
Day 23- Privileges
Day 24- Sql Server Logins
Day 25- Sql Server Roles
Day 26- Sql Server BackUp
Day 27- Why Temporary Table
Day 28- Why Table variable
Day 29- Some useful queries
Day 30- Useful tips
Day 2- SQL Server installation
Day 3- What is RDBMS?
Day 4- Normalization
Day 5- De-Normalization
Day 6- ACID Property
Day 7- Variables and Data Types
Day 8- Writing basic queries
Day 9- Writing Advance Queries
Day 10- SQL Server Constraints
Day 11- Aggregate Functions
Day 12- Sub Queries
Day 13- Joins
Day 14- Indexes
Day 15- Views
Day 16- Stored Procedures
Day 17- Triggers
Day 18- User Defined functions
Day 19- Linked Server
Day 20- Cursor
Day 21- Collation
Day 22- Transactions
Day 23- Privileges
Day 24- Sql Server Logins
Day 25- Sql Server Roles
Day 26- Sql Server BackUp
Day 27- Why Temporary Table
Day 28- Why Table variable
Day 29- Some useful queries
Day 30- Useful tips
Day 1- Introduction to MS SQL Server
Microsoft SQL Server is a relational database management system which is designed to run from single desktop machine to multiprocessor huge servers. Basically SQL Server is used to store the large data of websites as well as of CRMs.
SQL Server uses a type of database called a relational database. In relational databases, data is organized into tables.Tables are organized by grouping data about the same subject and contain columns and rows of information.
As you saw, a relational database is composed of different types of objects. The following are some of the more common objects:
1- Tables are the objects that contain the data types and actual raw data.
2- Columns are the parts of the tables holding the data.
3- Data types are the base storage type of your data.
4- Stored procedures are like macros in that SQL code that can be written and stored under a name.
5- User defined functions are transact SQL code thats very similar to stored procedures.
6- Triggers are stored procedures that activate either before or after data is added, modified or deleted from the database.
7- Views are basically queries stored in the database that can reference one or many tables.
8- Index can help organize data so that queries run faster.
9- Primary keys are essential to relational database. They enforce uniqueness among rows, providing a way to uniquely identify
every item you want to store.
10- Foreign key are one or more columns that reference the primary keys or unique constraints of other tables.
All above mentioned objects are described in brief. There are other objects as well which will be described in details among with above mentioned objects in this tutorial latter.
In the next chapter we will learn how to install SQL Server onto your machine, in a network environment.
Previous
Index
Next
SQL Server uses a type of database called a relational database. In relational databases, data is organized into tables.Tables are organized by grouping data about the same subject and contain columns and rows of information.
As you saw, a relational database is composed of different types of objects. The following are some of the more common objects:
1- Tables are the objects that contain the data types and actual raw data.
2- Columns are the parts of the tables holding the data.
3- Data types are the base storage type of your data.
4- Stored procedures are like macros in that SQL code that can be written and stored under a name.
5- User defined functions are transact SQL code thats very similar to stored procedures.
6- Triggers are stored procedures that activate either before or after data is added, modified or deleted from the database.
7- Views are basically queries stored in the database that can reference one or many tables.
8- Index can help organize data so that queries run faster.
9- Primary keys are essential to relational database. They enforce uniqueness among rows, providing a way to uniquely identify
every item you want to store.
10- Foreign key are one or more columns that reference the primary keys or unique constraints of other tables.
All above mentioned objects are described in brief. There are other objects as well which will be described in details among with above mentioned objects in this tutorial latter.
In the next chapter we will learn how to install SQL Server onto your machine, in a network environment.
Previous
Index
Next
RDBMS Basics
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, Oracle, MySQL, and Microsoft Access.
In Simple word RDBMS is a database management system that is based on the relational model as introduced by E. F. Codd.
Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson.
In fact, most of today's database systems are referred to as a RDBMS, because of their ability to store related data across multiple tables.
Some of the more popular relational database management systems include:
* Microsoft Access
* Microsoft SQL Server
* MySQL
* Oracle
In Short below are the basics of RDBMS:-
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.
The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.
Every table is broken up into smaller entities called fields.
A record, also called a row of data, is each individual entry that exists in a table. A column is a vertical entity in a table that contains all information associated with a specific field in a table.
In Simple word RDBMS is a database management system that is based on the relational model as introduced by E. F. Codd.
Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson.
In fact, most of today's database systems are referred to as a RDBMS, because of their ability to store related data across multiple tables.
Some of the more popular relational database management systems include:
* Microsoft Access
* Microsoft SQL Server
* MySQL
* Oracle
In Short below are the basics of RDBMS:-
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.
The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.
Every table is broken up into smaller entities called fields.
A record, also called a row of data, is each individual entry that exists in a table. A column is a vertical entity in a table that contains all information associated with a specific field in a table.
Useful Queries (Group By example)
Find out the results on the basis of grouping
Sometimes we got the requirement where we have to group the results on the basis of a column. Let me explain you steps by step:-
Step 1: Create a table first
CREATE TABLE groupingExample(
id INT IDENTITY (1, 1)
,exc_idn INT
,lot_nbr VARCHAR(20)
)
GO
Step 2: Insert the sample record into this table
INSERT INTO groupingExample (exc_idn, lot_nbr)
SELECT 5760441, 'F0389441'
UNION ALL
SELECT 5760441, 'F0389541'
UNION ALL
SELECT 5760517, 'F0367191'
UNION ALL
SELECT 5760517, 'F0384651'
UNION ALL
SELECT 5760523, 'F0367191'
UNION ALL
SELECT 5760523, 'F0384651'
UNION ALL
SELECT 5760643, 'F0366961'
UNION ALL
SELECT 5760885, 'F0365977'
UNION ALL
SELECT 5764109, 'F0312698'
UNION ALL
SELECT 5764109, 'F0312671'
GO
Step 3: Now write a query which will give that each exc_idn contain how many lot_nbr in this table. Let's do it in this way
SELECT exc_idn, COUNT(lot_nbr) COUN FROM groupingExample GROUP BY exc_idn
And the output would be something like
exc_idn COUN
5760441 2
5760517 2
5760523 2
5760643 1
5760885 1
5764109 2
You can also use the order by clause with the above query like
SELECT exc_idn, COUNT(lot_nbr) COUNT FROM groupingExample GROUP BY exc_idn order by 2 desc
Thanks.
Sometimes we got the requirement where we have to group the results on the basis of a column. Let me explain you steps by step:-
Step 1: Create a table first
CREATE TABLE groupingExample(
id INT IDENTITY (1, 1)
,exc_idn INT
,lot_nbr VARCHAR(20)
)
GO
Step 2: Insert the sample record into this table
INSERT INTO groupingExample (exc_idn, lot_nbr)
SELECT 5760441, 'F0389441'
UNION ALL
SELECT 5760441, 'F0389541'
UNION ALL
SELECT 5760517, 'F0367191'
UNION ALL
SELECT 5760517, 'F0384651'
UNION ALL
SELECT 5760523, 'F0367191'
UNION ALL
SELECT 5760523, 'F0384651'
UNION ALL
SELECT 5760643, 'F0366961'
UNION ALL
SELECT 5760885, 'F0365977'
UNION ALL
SELECT 5764109, 'F0312698'
UNION ALL
SELECT 5764109, 'F0312671'
GO
Step 3: Now write a query which will give that each exc_idn contain how many lot_nbr in this table. Let's do it in this way
SELECT exc_idn, COUNT(lot_nbr) COUN FROM groupingExample GROUP BY exc_idn
And the output would be something like
exc_idn COUN
5760441 2
5760517 2
5760523 2
5760643 1
5760885 1
5764109 2
You can also use the order by clause with the above query like
SELECT exc_idn, COUNT(lot_nbr) COUNT FROM groupingExample GROUP BY exc_idn order by 2 desc
Thanks.
Roll Up Multiple Rows into a single rows and column.
Sometimes we got a requirement where we have to bind the multiple rows into a single row and column. For example the requirement is like, we have a table called tblCategory (parent table) and another table tblBrands (child table) which stored the data as following:-
tblCategory
tblCategory
tblBrands
And we want the result display as:
Now we have to find out the best way to achieve this requirement.
The solutions which we are going to explore will use two SQL commands STUFF and FOR XML. We will explain about these commands later in this tutorial.
As all of us will be aware about INNER JOIN so first lets write it in very simple manner:-
SELECT c.cat_name
,b.brand_nme
FROM tblCategory c
JOIN tblBrands b ON b.cat_id = c.id
ORDER BY 1, 2
And we will get the results in following way
Lets take one step ahead and use FOR XML PATH option which will return the result as XML string and will put all the data into one row and column.
SELECT c.cat_name
,b.brand_nme
FROM tblCategory c
JOIN tblBrands b ON b.cat_id = c.id
ORDER BY 1, 2
FOR XML PATH ('')
And here the result will appear as
Now try to convert the join into part of the select statement
SELECT c.cat_name
,(SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')) [Section]
FROM tblCategory c
ORDER BY 1
The result would be like
Now finally use the STUFF command to fulfill our requirement
SELECT c.cat_name
,STUFF((SELECT '; '+b.brand_nme FROM tblBrands b WHERE b.cat_id = c.id FOR XML PATH('')), 1, 1, '') [Section]
FROM tblCategory c
GROUP BY c.cat_name, c.id
ORDER BY 1
And here we go!
There might be other best options to achieve this requirement but this one I used during my project. Other best solutions and comments are also most welcome!
Useful queries (inner join by example)
Find out the common records from two tables
Sometimes we need to figure out the common records from two tables. Here we figure it out step by step:-
Step 1 - Create two tables as below:
CREATE TABLE tblA(
id INT
,name VARCHAR(20)
)
GO
CREATE TABLE tblB(
id INT
,name VARCHAR(20)
)
GO
Step 2 - Now insert some records into these two table as below:
INSERT INTO tblA VALUES(1, 'Alok')
INSERT INTO tblA VALUES(2, 'Ashu')
INSERT INTO tblA VALUES(3, 'Manu')
INSERT INTO tblA VALUES(4, 'Ravi')
INSERT INTO tblA VALUES(5, 'Baby')
GO
INSERT INTO tblB VALUES(1, 'Alok')
INSERT INTO tblB VALUES(2, 'Ashu')
INSERT INTO tblB VALUES(3, 'Manu')
INSERT INTO tblB VALUES(4, 'Ravi')
INSERT INTO tblB VALUES(6, 'Raju')
GO
Step 3 - Now write the query to find out the common records:
SELECT a1.id, a1.name, a2.id, a2.name
FROM tblA a1
JOIN tblB a2 ON a1.id = a2.id
GO
And the result would be like this
id name id name
1 Alok 1 Alok
2 Ashu 2 Ashu
3 Manu 3 Manu
4 Ravi 4 Ravi
Thanks.
Sometimes we need to figure out the common records from two tables. Here we figure it out step by step:-
Step 1 - Create two tables as below:
CREATE TABLE tblA(
id INT
,name VARCHAR(20)
)
GO
CREATE TABLE tblB(
id INT
,name VARCHAR(20)
)
GO
Step 2 - Now insert some records into these two table as below:
INSERT INTO tblA VALUES(1, 'Alok')
INSERT INTO tblA VALUES(2, 'Ashu')
INSERT INTO tblA VALUES(3, 'Manu')
INSERT INTO tblA VALUES(4, 'Ravi')
INSERT INTO tblA VALUES(5, 'Baby')
GO
INSERT INTO tblB VALUES(1, 'Alok')
INSERT INTO tblB VALUES(2, 'Ashu')
INSERT INTO tblB VALUES(3, 'Manu')
INSERT INTO tblB VALUES(4, 'Ravi')
INSERT INTO tblB VALUES(6, 'Raju')
GO
Step 3 - Now write the query to find out the common records:
SELECT a1.id, a1.name, a2.id, a2.name
FROM tblA a1
JOIN tblB a2 ON a1.id = a2.id
GO
And the result would be like this
id name id name
1 Alok 1 Alok
2 Ashu 2 Ashu
3 Manu 3 Manu
4 Ravi 4 Ravi
Thanks.
Useful queries (self join by example)
How to find out the Manager of respective employee from a single table (Self Join by example)
Suppose here is a table called employee:-
empId name mngrId
1 Alok 2
2 Ashu 3
3 Manu 4
4 Ravi 5
5 Baby 1
Now the requirement is to find out the manager of each employee. Lets do it step by step:-
Step 1- Create a table with following statement:
CREATE TABLE employee(
empid INT
,name VARCHAR(20)
,mngrId INT
)
GO
Step 2- Insert the data into employee table:
INSERT INTO employee SELECT 1, 'Alok', 2
INSERT INTO employee SELECT 2, 'Ashu', 3
INSERT INTO employee SELECT 3, 'Manu', 4
INSERT INTO employee SELECT 4, 'Ravi', 5
INSERT INTO employee SELECT 5, 'Baby', 1
GO
Step 3- Write the query to fetch the required result:
SELECT e1.name empName, e2.name mngrName
FROM employee e1
JOIN employee e2 ON e1.mngrId = e2.empid
GO
Thanks.
Suppose here is a table called employee:-
empId name mngrId
1 Alok 2
2 Ashu 3
3 Manu 4
4 Ravi 5
5 Baby 1
Now the requirement is to find out the manager of each employee. Lets do it step by step:-
Step 1- Create a table with following statement:
CREATE TABLE employee(
empid INT
,name VARCHAR(20)
,mngrId INT
)
GO
Step 2- Insert the data into employee table:
INSERT INTO employee SELECT 1, 'Alok', 2
INSERT INTO employee SELECT 2, 'Ashu', 3
INSERT INTO employee SELECT 3, 'Manu', 4
INSERT INTO employee SELECT 4, 'Ravi', 5
INSERT INTO employee SELECT 5, 'Baby', 1
GO
Step 3- Write the query to fetch the required result:
SELECT e1.name empName, e2.name mngrName
FROM employee e1
JOIN employee e2 ON e1.mngrId = e2.empid
GO
Thanks.
Subscribe to:
Posts (Atom)