All about Sql Server: Pivot Unpivot data in Sql server: Pivot Unpivot data Pivot example in sql server hi, first we will see what is pivot and unpivot,uses in sql server ...
Categories
- Advance SQL (20)
- Interview QA (4)
- Knowledge Queries (34)
- SQL BASIC (12)
- SQL Functions (2)
- SQL Queries (4)
Saturday 7 September 2013
Saturday 27 July 2013
Normalization in sql server
Database normalization is the process of efficiently organizing data in a database.
There are two reasons
of the normalization process:
of the normalization process:
1) Eliminating redundant data,for example, storing the same data in more than one tables.
2) Ensuring data dependencies make sense.
2) Ensuring data dependencies make sense.
Both of these are worthy goals as they reduce the amount of space a database consumes and
ensure that data is logically stored. Normalization consists of a series of guidelines that
help guide you in creating a good database structure. Normalization guidelines are divided
into normal forms; think of form as the format or the way a database structure is laid out.
The aim of normal forms is to organize the database structure so that it complies with the
rules of first normal form, then second normal form, and finally third normal form.
It's your choice to take it further and go to fourth normal form, fifth normal form, and so on,
but generally speaking, third normal form is enough.
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
A) First Normal Form ::
First normal form (1NF) sets the very basic rules for an organized database:
1) Define the data items required, because they become the columns in a table.Place related data
items in a table.
2) Ensure that there are no repeating groups of data.
3) Ensure that there is a primary key.
1) Define the data items required, because they become the columns in a table.Place related data
items in a table.
2) Ensure that there are no repeating groups of data.
3) Ensure that there is a primary key.
a)First Rule of 1NF:
You must define the data items. This means looking at the data to be stored, organizing the data into columns,
defining what type of data each column contains, and finally putting related columns into their own table.
For example, you put all the columns relating to locations of meetings in the Location table, those relating
to members in the MemberDetails table, and so on.
defining what type of data each column contains, and finally putting related columns into their own table.
For example, you put all the columns relating to locations of meetings in the Location table, those relating
to members in the MemberDetails table, and so on.
b)Second Rule of 1NF:
The next step is ensuring that there are no repeating groups of data.
Consider we have following table:
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25),
ORDERS VARCHAR(155) );
So if we populate this table for a single customer having multiple orders then it would be something as follows:
ID NAME AGE ADDRESS ORDERS 100 Sachin 36 Lower West Side Cannon XL-200 100 Sachin 36 Lower West Side
Battery XL-200 100 Sachin 36 Lower West Side Tripod Large But as per 1NF,
Battery XL-200 100 Sachin 36 Lower West Side Tripod Large But as per 1NF,
we need to ensure that there are no repeating groups of data. So let us break above table into to parts
and join them using a key as follows:
and join them using a key as follows:
CUSTOMERS table:
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25),
PRIMARY KEY (ID) );
PRIMARY KEY (ID) );
This table would have following record:
ID NAME AGE ADDRESS 100 Sachin 36
Lower West Side ORDERS table:
CREATE TABLE ORDERS( ID INT NOT NULL, CUSTOMER_ID INT NOT NULL, ORDERS VARCHAR(155),
PRIMARY KEY (ID) );
PRIMARY KEY (ID) );
This table would have following records:
ID CUSTOMER_ID ORDERS 10 100 Cannon XL-200 11 100 Battery XL-200 12 100
Third Rule of 1NF:
The final rule of the first normal form .
create a primary key for each table which we have already created. Second Normal Form Second normal
form states that it should meet all the rules for 1NF and there must be no partial dependences of any
of the columns on the primary key:Consider a customer-order relation and you want to store customer ID,
customer name, order ID and order
detail,and date of purchage:
create a primary key for each table which we have already created. Second Normal Form Second normal
form states that it should meet all the rules for 1NF and there must be no partial dependences of any
of the columns on the primary key:Consider a customer-order relation and you want to store customer ID,
customer name, order ID and order
detail,and date of purchage:
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, ORDER_ID INT NOT NULL,
ORDER_DETAIL VARCHAR (20) NOT NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) );
ORDER_DETAIL VARCHAR (20) NOT NULL, SALE_DATE DATETIME, PRIMARY KEY (CUST_ID, ORDER_ID) );
This table is in first normal form, in that it obeys all the rules of first normal form. In this table,
the primary key consists of CUST_ID and ORDER_ID. Combined they are unique assuming same
customer would hardly order same thing. However, the table is not in second normal form because
there are partial dependencies
of primary keys and columns. CUST_NAME is dependent on CUST_ID,
and there's no real link between a customer's name and what he purchaged.
Order detail and purchage date are also dependent on ORDER_ID, but they are not dependent on
CUST_ID,
because there's no link between a CUST_ID and an ORDER_DETAIL or their SALE_DATE. To make
this table comply
with second normal form, you need to separate the columns into three tables. First,
the primary key consists of CUST_ID and ORDER_ID. Combined they are unique assuming same
customer would hardly order same thing. However, the table is not in second normal form because
there are partial dependencies
of primary keys and columns. CUST_NAME is dependent on CUST_ID,
and there's no real link between a customer's name and what he purchaged.
Order detail and purchage date are also dependent on ORDER_ID, but they are not dependent on
CUST_ID,
because there's no link between a CUST_ID and an ORDER_DETAIL or their SALE_DATE. To make
this table comply
with second normal form, you need to separate the columns into three tables. First,
create a table to store the customer details as follows:
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, PRIMARY KEY (CUST_ID) );
Next, create a table to store details of each order:
CREATE TABLE ORDERS( ORDER_ID INT NOT NULL, ORDER_DETAIL VARCHAR (20) NOT NULL, PRIMARY KEY (ORDER_ID) );
Finally, create a third table storing just CUST_ID and ORDER_ID to keep track of all the orders for a
customer:
customer:
CREATE TABLE CUSTMERORDERS( CUST_ID INT NOT NULL, ORDER_ID INT NOT NULL, SALE_DATE DATETIME, PRIMARY KEY
(CUST_ID, ORDER_ID) );
(CUST_ID, ORDER_ID) );
Third Normal Form A table is in third normal form when the following conditions are met:
It is in second normal form.
All nonprimary fields are dependent on the primary key. The dependency of nonprimary fields is
between the data.
All nonprimary fields are dependent on the primary key. The dependency of nonprimary fields is
between the data.
For example in the below table, street name, city, and state are unbreakably bound to the zip code.
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, STREET VARCHAR(200),
CITY VARCHAR(100), STATE VARCHAR(100), ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );
CITY VARCHAR(100), STATE VARCHAR(100), ZIP VARCHAR(12), EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );
The dependency between zip code and address is called a transitive dependency.
To comply with third normal form, all you need to do is move the Street, City, and State fields into
their own table, which you can call the Zip Code table:
To comply with third normal form, all you need to do is move the Street, City, and State fields into
their own table, which you can call the Zip Code table:
CREATE TABLE ADDRESS( ZIP VARCHAR(12), STREET VARCHAR(200), CITY VARCHAR(100), STATE VARCHAR(100), PRIMARY KEY (ZIP) );
Next, alter the CUSTOMERS table as follows:
CREATE TABLE CUSTOMERS( CUST_ID INT NOT NULL, CUST_NAME VARCHAR (20) NOT NULL, DOB DATE, ZIP VARCHAR(12),
EMAIL_ID VARCHAR(256), PRIMARY KEY (CUST_ID) );
The advantages of removing transitive dependencies are mainly twofold. First,
the amount of data duplication is reduced and therefore your database becomes smaller.
The second advantage is data integrity. When duplicated data changes, there's a big risk of updating
only some of the data, especially if it's spread out in a number of different places in the database.
For example, If address and zip code data were stored in three or four different tables, then any
changes in zip codes would need to ripple out to every record in those three or four tables.
the amount of data duplication is reduced and therefore your database becomes smaller.
The second advantage is data integrity. When duplicated data changes, there's a big risk of updating
only some of the data, especially if it's spread out in a number of different places in the database.
For example, If address and zip code data were stored in three or four different tables, then any
changes in zip codes would need to ripple out to every record in those three or four tables.
I hope You got something useful in this article.
I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
Your valuable feedback, question, or comments about this article are always welcome.
Friday 12 July 2013
basic Syntax in sql server
Hi ,
SQL is followed by unique set of rules and guidelines called Syntax. This Article gives
you a quick start with SQL by listing all the basic SQL Syntax: All the SQL statements start
with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP,
CREATE, USE, SHOW and all the statements end with a semicolon (;). Important point
to be noted is that SQL is case insensitive which means SELECT and select have same meaning
in SQL statements but MySQL make difference in table names. So if you are working with
MySQL then you need to give table names as they exist in the database.
SQL is followed by unique set of rules and guidelines called Syntax. This Article gives
you a quick start with SQL by listing all the basic SQL Syntax: All the SQL statements start
with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP,
CREATE, USE, SHOW and all the statements end with a semicolon (;). Important point
to be noted is that SQL is case insensitive which means SELECT and select have same meaning
in SQL statements but MySQL make difference in table names. So if you are working with
MySQL then you need to give table names as they exist in the database.
SELECT Statement:
SELECT column1, column2....columnN FROM table_name;
DISTINCT Clause:
SELECT DISTINCT column1, column2....columnN FROM table_name;
WHERE Clause:
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
AND/OR Clause:
SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2;
IN Clause:
SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);
BETWEEN Clause:
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;
Like :
SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };
ORDER BY :
SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};
GROUP BY :
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;
COUNT :
SELECT COUNT(column_name) FROM table_name WHERE CONDITION;
HAVING :
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);
CREATE TABLE Statement::
CREATE TABLE table_name(
column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
DROP TABLE Statement::
DROP TABLE table_name;
CREATE INDEX :
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
DROP INDEX :
ALTER TABLE table_name DROP INDEX index_name;
TRUNCATE :
TRUNCATE TABLE table_name;
ALTER :
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
Statement (Rename):
ALTER TABLE table_name RENAME TO new_table_name;
INSERT INTO :
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);
UPDATE:
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];
DELETE Statement:
DELETE FROM table_name WHERE {CONDITION};
CREATE DATABASE :
CREATE DATABASE database_name;
DROP :
DROP DATABASE database_name;
USE :
USE DATABASE database_name;
I hope You got something useful in this article.
I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
Your valuable feedback, question, or comments about this article are always welcome.
Related Posts
Group by in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Constraints in SQL
Hi,
In This artcle we will see about Constraints in sql indetails.
In This artcle we will see about Constraints in sql indetails.
SQL Constraints:
Constraints are the rules enforced on data columns on table.These are used to limit the type of data thatcan go into a table.This ensures the accuracy and reliability of the data in the database.
Contraints could be column level or table level. Column level constraints are applied only
to one column where as table level constraints are applied to the whole table.
Following are commonly used constraints available in SQL
NOT NULL Constraint:
Ensures that a column cannot have NULL value.
Ensures that a column cannot have NULL value.
DEFAULT Constraint :
Provides a default value for a column when none is specified.
Provides a default value for a column when none is specified.
UNIQUE Constraint:
Ensures that all values in a column are different.
Ensures that all values in a column are different.
PRIMARY Key:
Uniquely identified each rows/records in a database table.
Uniquely identified each rows/records in a database table.
FOREIGN Key:
Uniquely identified a rows/records in any another database table.
Uniquely identified a rows/records in any another database table.
CHECK Constraint:
The CHECK constraint ensures that all values in a column satisfy certain conditions.
The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX:
Use to create and retrieve data from the database very quickly.
Use to create and retrieve data from the database very quickly.
NOT NULL Constraint:
By default, a column can hold NULL values. If you do not want a column to have a NULL value then
you need to define such constraint on this column specifying that NULL is now not allowed for that column
. A NULL is not the same as no data, rather, it represents unknown data.
By default, a column can hold NULL values. If you do not want a column to have a NULL value then
you need to define such constraint on this column specifying that NULL is now not allowed for that column
. A NULL is not the same as no data, rather, it represents unknown data.
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three
of which, ID and NAME and AGE, specify not to accept NULLs
For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three
of which, ID and NAME and AGE, specify not to accept NULLs
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR
(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
DEFAULT Constraint:
The DEFAULT constraint provides a default value to a column when the INSERT INTO statement
does not provide a specific value.
The DEFAULT constraint provides a default value to a column when the INSERT INTO statement
does not provide a specific value.
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here SALARY column is set to 5000.00 by default, so in case INSERT INTPO statement
does not provide a value for this column then by default this
column would be set to 5000.00.
For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here SALARY column is set to 5000.00 by default, so in case INSERT INTPO statement
does not provide a value for this column then by default this
column would be set to 5000.00.
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) );
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) );
UNIQUE Constraint:
The UNIQUE Constraint prevents two records from having identical values in a particular column.
The UNIQUE Constraint prevents two records from having identical values in a particular column.
Example:
you might want to prevent two or more people from having identical age. For example, the following SQL
creates a new table called CUSTOMERS and adds five columns. Here AGE column is set to UNIQUE, so
that you can not have two records with same age:
you might want to prevent two or more people from having identical age. For example, the following SQL
creates a new table called CUSTOMERS and adds five columns. Here AGE column is set to UNIQUE, so
that you can not have two records with same age:
CREATE TABLE CUSTOMERS( ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
PRIMARY Key:
A primary key is a field in a table which uniquely identifies the each rows/records in a database table.
Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key which may consist of single or multiple fields.
When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s) then you can not have two records having the same value
of that field(s).
A primary key is a field in a table which uniquely identifies the each rows/records in a database table.
Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key which may consist of single or multiple fields.
When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s) then you can not have two records having the same value
of that field(s).
Example:
Note: You would use these concepts while creating database tables. Create Primary Here is the syntax to
define ID attribute as a primary key in a CUSTOMERS table.
Note: You would use these concepts while creating database tables. Create Primary Here is the syntax to
define ID attribute as a primary key in a CUSTOMERS table.
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
FOREIGN Key:
A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Primary key field from one table and insert it into the other table where it becomes a foreign key
ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the
second table. If a table has a primary key defined on any field(s) then you can not have two records having
the same value of that field(s).
A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Primary key field from one table and insert it into the other table where it becomes a foreign key
ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the
second table. If a table has a primary key defined on any field(s) then you can not have two records having
the same value of that field(s).
Example:
Consider the structure of the two tables as follows:
Consider the structure of the two tables as follows:
CUSTOMERS table:
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
ORDERS table:
CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double, PRIMARY KEY (ID) );
AMOUNT double, PRIMARY KEY (ID) );
CHECK Constraint:
The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates to false, the record violates the constraint and isn.t entered into the table.
The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates to false, the record violates the constraint and isn.t entered into the table.
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns.
Here we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18)
, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
select Group_by Comma Separated
Hi ,
In this article we will see how to get a group by comma separated values sql server.
In this article we will see how to get a group by comma separated values sql server.
Check Below example ::
step1:: Create table and fill some data in that table.
Create table #Group_by_CommaSeprated(id int ,Hosp_name varchar(50),City varchar(50))
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'sandip','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'raj','pune')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'ajit','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'aditya','nashik')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rachit','bhopal')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rakesh','bihar')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'shrikant','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'chandan','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'dishal','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'saket','UP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'pranav','sangli')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'aditya','kolhapur')
step 2:: Check your data .
select * from #Group_by_CommaSeprated
Step 3:: Select Query for get a group by comma separated values sql server.
SELECT id, Hosp_name =
STUFF((SELECT ', ' + Hosp_name
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, ''), City =
STUFF((SELECT ', ' + City
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #Group_by_CommaSeprated a
GROUP BY id
Step 4:: OUTPUT
I hope You got something useful in this article.
I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
Your valuable feedback, question, or comments about this article are always welcome.
Related Posts
Group by in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Tuesday 9 July 2013
magic tables in sql server
Hi ,
In this article we will see magic table in details.
In this article we will see magic table in details.
What is magic tables?
There are Inserted and Deleted logical tables in SQL Server.These tables are automatically created and managed
by SQL Server internally to hold recently inserted,deleted and updated values during DML operations
(Insert,Update,Delete) on a database table.
both inserted and deleted table called as magic tables.
Basically, logical tables are used by triggers.
by SQL Server internally to hold recently inserted,deleted and updated values during DML operations
(Insert,Update,Delete) on a database table.
both inserted and deleted table called as magic tables.
Basically, logical tables are used by triggers.
A)Inserted logical Table::
The Inserted table holds the recently inserted or updated values means new data values.
Hence newly added and updated records are inserted into the Inserted table.
Hence newly added and updated records are inserted into the Inserted table.
For example of Inserted logical Table .CLICK HERE
B)Deleted logical Table::
The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.
Hence old updated and deleted records are inserted into the Deleted table.
For example of Deleted logical Table .
CLICK HERE
We could not create the logical tables or modify the data with in the logical tables.Except
triggers,
When you use the OUTPUT clause in your query, logical tables are automatically created and managed
by SQL Server. OUTPUT clause also has access toInserted and Deleted logical tables just like triggers.
I hope You got something useful in this article.I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
When you use the OUTPUT clause in your query, logical tables are automatically created and managed
by SQL Server. OUTPUT clause also has access toInserted and Deleted logical tables just like triggers.
I hope You got something useful in this article.I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
Other Popular Posts ::
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
deleted Magic table in sql server
Deleted logical Table::
The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.
The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.
Example::
Suppose we have emp_info . Now We need to create two triggers to see data with in logical tables
Inserted and Deleted.
Suppose we have emp_info . Now We need to create two triggers to see data with in logical tables
Inserted and Deleted.
Step 1::Create Table
create table emp_info (empno int,ename varchar(100),manage_id int)
Step 2 ::CREATE TRIGGER on emp_info
CREATE TRIGGER trg_Emp_Ins
ON emp_info
FOR UPDATE
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end
Step 3::Now Update a record in emp_info table to see data with in deleted logical table.
update emp_info set ename='Sandip Gaikwad', manage_id=3 where empno=3
SELECT * FROM emp_info
Step 4::OUTPUT
Other Popular Posts ::
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
Inserted Magic table with INSERT statement In sql sever
Hi,
In this article we will see example of logical table or magic table when we insert data in sql table.
In this article we will see example of logical table or magic table when we insert data in sql table.
Example::
Suppose we have emp_info . Now We need to create two triggers to see data with
in logical tables Inserted and Deleted.
Suppose we have emp_info . Now We need to create two triggers to see data with
in logical tables Inserted and Deleted.
Step 1::Create Table
create table emp_info (empno int,ename varchar(100),manage_id int)
Step 2 :: CREATE TRIGGER on emp_info
CREATE TRIGGER trg_Emp_Ins
ON emp_info
FOR INSERT
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end
Step 3::Now insert a new record in emp_info table to see data with in Inserted logical table.
INSERT INTO emp_info(empno, ename, manage_id) VALUES(3,'Sandip G',3)
SELECT * FROM emp_info
I hope You got something useful in this article.I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.
Other Popular Posts ::
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
self join in sql sever
Self Join::
A self join is a join in which a table is joined with itself,specially when the table has
a foreign key which references its own primary key.
in other words self join means two instances of the same table will be joined
in the query.
for your better understanding Check below example.
Step 1 ::Create Table and fill some data in that table
create table #emp_info (empno int,ename varchar(100),manage_id int)
insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',1)
insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)
Step 2 :: Query for self join
Select e.empno, e.ename, m.ename 'Manager' from #emp_info m,
#emp_info e where e.manage_id =m.empno
in above query we are apply join on two instance e and m of #emp_info
Step 3 :: Output
Related Posts ::
Swap values of two columns in sql server
Hi ,
Suppose In case of data uploading or data inserting you done mistake to upload or insert
data of one column to other then you want to swap the values of that two columns of a table
in SQL Server.
Suppose you have a #emp_info table in the database with the following data and
you want to interchange the values of empno and then how do you do?
Suppose In case of data uploading or data inserting you done mistake to upload or insert
data of one column to other then you want to swap the values of that two columns of a table
in SQL Server.
Suppose you have a #emp_info table in the database with the following data and
you want to interchange the values of empno and then how do you do?
r
create table #emp_info (empno int,ename varchar(100),manage_id int)
insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',2)
insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)
create table #emp_info (empno int,ename varchar(100),manage_id int)
insert into #emp_info (empno,ename ,manage_id )values(1,'sandip',3)
insert into #emp_info (empno,ename ,manage_id )values(2,'harshal',1)
insert into #emp_info (empno,ename ,manage_id )values(3,'aditya',4)
insert into #emp_info (empno,ename ,manage_id )values(4,'pranav',2)
insert into #emp_info (empno,ename ,manage_id )values(5,'ajit',3)
Your Data ::
select * from #emp_info
Don't worry, to do this task, you need to just write a simple update query for Customer table like as :
UPDATE #emp_info SET empno=manage_id , manage_id=empno
Other Popular Posts ::
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
Insert value in Identity column of table
Hi,
In This article we will see how to insert value in Identity column.Identity field
is usually used as a primary key. When you insert a new record into your table,
this field automatically assign an incremented value from the previous entry.
Usually, you can't insert your own value to this field.
In this article, I am going to expose the tips for inserting your own value to this field.
It is simple and easy. Consider you have the following Emp_Info table.
In This article we will see how to insert value in Identity column.Identity field
is usually used as a primary key. When you insert a new record into your table,
this field automatically assign an incremented value from the previous entry.
Usually, you can't insert your own value to this field.
In this article, I am going to expose the tips for inserting your own value to this field.
It is simple and easy. Consider you have the following Emp_Info table.
CREATE TABLE Emp_Info
(
ID int IDENTITY,
Emp_Name varchar(100),
Emp_Address varchar(200)
)
Now, I am trying to insert a record into Emp_Info table with
identity field like as then I will get the error message as shown below.
identity field like as then I will get the error message as shown below.
INSERT INTO Emp_Info(ID,Emp_Name,Emp_Address) VALUES(1,'Ajit Gaikwad','sangli')
Error::
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Emp_Info' when IDENTITY_INSERT is set to OFF.
A) Allow insert into identity field
You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular
table as shown:
You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular
table as shown:
SET IDENTITY_INSERT Emp_Info ON
B) Disallow insert into identity field
You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a
particular table as shown:
You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a
particular table as shown:
SET IDENTITY_INSERT Emp_Info OFF
Test:: Insert Value to Identity field
Now, lets see how to insert our own values to identity field ID with in the Emp_Info table.
Now, lets see how to insert our own values to identity field ID with in the Emp_Info table.
SET IDENTITY_INSERT Emp_Info ON
INSERT INTO Emp_Info(ID,Name,Address) VALUES(3,'harshal','sangli')
INSERT INTO Emp_Info(ID,Name,Address) VALUES(4,'ram','sangli')
SET IDENTITY_INSERT Emp_Info OFF
INSERT INTO Emp_Info(Name,Address) VALUES('rakesh','bihar')
INSERT INTO Emp_Info(ID,Name,Address) VALUES(3,'harshal','sangli')
INSERT INTO Emp_Info(ID,Name,Address) VALUES(4,'ram','sangli')
SET IDENTITY_INSERT Emp_Info OFF
INSERT INTO Emp_Info(Name,Address) VALUES('rakesh','bihar')
After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.
Other Popular Posts ::
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Saturday 6 July 2013
Check all posts
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Create html table by using sql store procedure
Hi ,
In this article we will see how to create html table inside html table
by using sql server to show data of select statement.
In this article we will see how to create html table inside html table
by using sql server to show data of select statement.
step 1:: Create table
CREATE TABLE #TEMP (EMP_NAME VARCHAR(100),CHILD_NAME VARCHAR(100),CHILD_HOBBY VARCHAR(100),CHILD_AGE INT)
step 2:: Insert data in table
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','CRI',2)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','READ',2)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','CRI',4)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','READ',4)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANds','TE',24)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANsd','ME',42)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','JI',23)
insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','CI',23)
step 3:: Use below query for select data and create html table inside html table.
step 4::Output
select column data as row by using sql server
Hi,
In this article we will discuss about how to select or display column data as row by using sql server.
In this article we will discuss about how to select or display column data as row by using sql server.
1) Create table
CREATE TABLE YourTable
(RowID int primary key not null identity(1,1)
,emp_id int null
,emp_name varchar(100)
,Last_name varchar(100)
,salary money
,StatusValue char(1)
,ranks int
)
2) Insert Records in to table
INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (1,'sandip','Gaikwad',1234.56,'A',12)
INSERT INTO YourTable (emp_id,emp_name,Last_name,salary,StatusValue,ranks) VALUES (2,'pranav','Gaikwad',14034.56,'I',102)
3) Check origanal data
3) Check below query to select column data as row.
DECLARE @YourTableName varchar(1000)
DECLARE @YourTableWhere varchar(1000)
DECLARE @YourQuery varchar(max)
SET @YourTableName='YourTable'
set @YourTableWhere='y.RowID=1'
SELECT
@YourQuery = STUFF(
(SELECT +
' UNION '
+ 'SELECT '''+COLUMN_NAME+''' as Col_name, CONVERT(varchar(max) ,'+COLUMN_NAME+') as column_data FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
FOR XML PATH('')
), 1, 7, ''
)
DECLARE @YourTableWhere varchar(1000)
DECLARE @YourQuery varchar(max)
SET @YourTableName='YourTable'
set @YourTableWhere='y.RowID=1'
SELECT
@YourQuery = STUFF(
(SELECT +
' UNION '
+ 'SELECT '''+COLUMN_NAME+''' as Col_name, CONVERT(varchar(max) ,'+COLUMN_NAME+') as column_data FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
FOR XML PATH('')
), 1, 7, ''
)
4) Check output ::
EXEC (@YourQuery)
pivot with dynamic column names in sql sever
Hi ,
In previous article we see how to pivot data with static column name. now we will
discuss about how to do privot data with dynamic column name. for your better understanding
create below table and fill it with some data.
In previous article we see how to pivot data with static column name. now we will
discuss about how to do privot data with dynamic column name. for your better understanding
create below table and fill it with some data.
Create table ::
create table #color (Color_id int,Color_name varchar(50),color_prize int)
Insert Data::
insert into #color (Color_id,Color_name,color_prize) values (1,'black',1)
insert into #color (Color_id,Color_name,color_prize) values (1,'red',2)
insert into #color (Color_id,Color_name,color_prize) values (1,'wt',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'re',3)
insert into #color (Color_id,Color_name,color_prize) values (1,'Grey',4)
insert into #color (Color_id,Color_name,color_prize) values (2,'black',6)
insert into #color (Color_id,Color_name,color_prize) values (2,'red',7)
insert into #color (Color_id,Color_name,color_prize) values (2,'wt',8)
insert into #color (Color_id,Color_name,color_prize) values (2,'re',9)
insert into #color (Color_id,Color_name,color_prize) values (2,'Grey',10)
get All column as comma separated string::
declare @Dynamic_column varchar(max)
select @Dynamic_column = COALESCE(@Dynamic_column+',' , '') +Color_name from (select distinct Color_name from #color)t
print @Dynamic_column
select @Dynamic_column = COALESCE(@Dynamic_column+',' , '') +Color_name from (select distinct Color_name from #color)t
print @Dynamic_column
write a dynamic Query for pivot with dynamic columns::
declare @query varchar(max)
set @query ='select * from
(
select Color_id,Color_name,color_prize from #color
)te
pivot
(
min(color_prize) for Color_name in('+@Dynamic_column +')
) as d'
exec(@query)
Thursday 27 June 2013
Get month wise Calender for given year
Hi,
In this article we will see how to get calender of all month in given year.
in privious article i have discused about get calender of given month of current year.
In this article we will see how to get calender of all month in given year.
in privious article i have discused about get calender of given month of current year.
Solution::
1) Create below procedures ::
CREATE PROCEDURE [dbo].[CALENDAR] (@YEAR int) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=1
WHILE @STARTMONTH<=12
BEGIN
SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATENAME(WEEK,@DATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT< =@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1
END
SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY FROM #TEMP
DROP TABLE #TEMP
SET @INPUTDATE=DATEADD(MM,1,@INPUTDATE)
SET @STARTMONTH=@STARTMONTH+1
END
1) Create below procedures ::
CREATE PROCEDURE [dbo].[CALENDAR] (@YEAR int) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=1
WHILE @STARTMONTH<=12
BEGIN
SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATENAME(WEEK,@DATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT< =@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1
END
SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY FROM #TEMP
DROP TABLE #TEMP
SET @INPUTDATE=DATEADD(MM,1,@INPUTDATE)
SET @STARTMONTH=@STARTMONTH+1
END
2)Test Case::
exec CALENDAR 2013
Friday 21 June 2013
Insert output of sql function to table
In this article we will see how to insert data or records from sql table value function .
example::
1)Create table::
Table1:
Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')
Table2:
Create table #temp (id int ,emp_name varchar(100))
2)Create Function::
CREATE FUNCTIONSql_fun
(
)
RETURNS TABLE
AS
RETURN (
select id,emp_name from emp
)
GO
3)insert from sql function to table::
insert into #temp (cp_code,cp_name)select * from Sql_fun()
select * from #temp
Related Posts
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Insert store procedure output to sql table
In this article we will see how to insert store procedure output
to sql table.
example::
1)Create table::
A)
Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')
B)
create proc proc_out
as
begin
select id,emp_name from emp
end
insert into #temp (cp_code,cp_name)exec cp
select * from #temp
1)Create table::
A)
Create table emp (id int ,emp_name varchar(100))
Insert into emp (id,emp_name)values(1,'raj')
Insert into emp (id,emp_name)values(2,'sham')
Insert into emp (id,emp_name)values(3,'ram')
Insert into emp (id,emp_name)values(4,'ss')
Insert into emp (id,emp_name)values(5,'d.raj')
Insert into emp (id,emp_name)values(6,'pranav')
Insert into emp (id,emp_name)values(7,'pranav')
Insert into emp (id,emp_name)values(8,'aditya')
Insert into emp (id,emp_name)values(9,'aditya')
B)
Create table #temp (id int ,emp_name varchar(100))
2)Create procedure::
create proc proc_out
as
begin
select id,emp_name from emp
end
3)Insert output of proc to table ::
insert into #temp (cp_code,cp_name)exec cp
select * from #temp
Related Posts
Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update
Trigger on insert
trigger on delete
basic of trigger
insert into view with multiple tables
Insert into View in sql server
Interview queries in sql server
update data in view with multiple table
Update view with single table
Group by in sql server
pivot without aggregate function in sql
loop in sql server
get monthwise calender in sql server
temp table and temp variable in sql server
Subscribe to:
Posts (Atom)