UNION and UNION ALL in sql server

Hi,
In This article we will What is UNION and UNION ALL? What is difference between both?
and details about both.

1) Union and Union All::-

The purpose of the SQL UNION
and UNION ALL commands are to combine the results of two or more queries
into a single result set consisting of all the rows belonging to all the queries
in the union.
but their is one condition a column count and datatype of all must same when you use union .
The question becomes whether or not to use the ALL syntax.

2)What is difference between Union and Union All?

The difference between Union and Union all is that Union all
will not eliminate duplicate rows, instead it just pulls all
rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set.
If you know that all the records returned are unique from your union,
use UNION ALL instead, it gives faster results.

In simple word a union is give you a distinct data from both query,and
union all is give all data with all duplicate records.in case of union all
their is no need to check duplicate records thats why a union all is faster than
union.

3)Check below query for understanding how to use UNION and UNION ALL

A) Create table and fill some data ::

 create table #emp(Emp_name varchar(100),dept varchar(50), emp_sal int)
insert into #emp(Emp_name ,dept , emp_sal)values('adi','IT',123)
insert into #emp(Emp_name ,dept , emp_sal)values('raj','IT',1231)
insert into #emp(Emp_name ,dept , emp_sal)values('nisha','WC',1232)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)


create table #manager(manager_name varchar(100),dept varchar(50), manager_sal int) insert into #manager(manager_name,dept ,manager_sal )values ('sandip','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('aditya','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('pranav','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)

B)UNION ::

select * from #emp union select * from #manager

C)UNION ALL ::

select * from #emp union all select * from #manager

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

Interview Question in sql server

Problem ::

Interchange 0 by 1 and 1 by 0 in given table ?

A most of time a interviewer asked this quetion,check below table.

Table Script with data ::

create table #temp (no_s int)

insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')

select * from #temp

Data ::

SCOPE_IDENTITY

SCOPE_IDENTITY (T-SQL) 

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

• In simple words SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

For better understanding simply follow below steps and observe results

step 1 :-

create table policy_master (policy_no int identity(1,1),policy_owner varchar(40))

insert into policy_master values ('sandip')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 2 :-

insert into policy_master values ('sandip2')

insert into policy_master values ('sandip3')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 3 :-

insert into policy_master values ('sandip4')

insert into policy_master values ('sandip5')

insert into policy_master values ('sandip6')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 4 :-

create trigger policy_trigger on policy_master

 instead of insert as

 insert into policy_master values ('sandipg')

step 5 :-

 insert into policy_master values ('sandip7')

 select SCOPE_IDENTITY() C_SCOPE_IDENTITY

if you closely observe last step, record is inserted in table policy_master through trigger written on policy_master table itself but scope_identity() function cannot show you last identity of policy master table because insert statement in trigger is out of scope for scope_identity().

                                  

 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.

TABS IN SSIS DESIGNER

What is SSIS Designer?
                      It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.

Control flow TAB:-
                     Use the Control Flow tab of SSIS Designer to build the control flow in an Integration Services package. By using control flow tab you can define your required functionality flow. Microsoft provides wide range of tools to design control flow like containers, for each loop etc.

Data flow tab:-
                       Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse. Three types for tools provide by Microsoft for data flow design 1) data source 2) transformations tools 3) destinations tools

Event Handlers tab:-
                                An event handler runs in response to an event raised by the package or by a task or container in the package. For example I don’t want fail my package on error or failure of one or more task in ssis package instead of that write mail to support team to look an fix issue.so for achieving this I will go to event handler tab and configure mail sending to task on failure event of those data flow or control flow task. We will discuss event handler in future post with real time example for better understanding.

Package Explorer tab:-
                           Package Explorer tab is useful for SSIS Designer to see a hierarchical view of all of the elements in a package: configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables

SQL server integration services(SSIS) introduction

    
         Microsoft SQL Server 2005 Integration Services (SSIS) is the tool set used to help you implement data integration process applications among your business application system’s files and databases. SSIS is much more than a simple extract, transform, and load (ETL) process

Basic Interactive Development Environment:-

ETL is stand for extract, transform and load:-

Transform Data: data are coming from various sources and we cannot assume that the data is structured in the same way across all the sources. We need to transform data in required format .SSIS provide large number transformation tools

 Load Data: Once the transformations are done and the data takes the form as per the requirement, we have to load the data to the destination systems.

 Load Data: Once the transformations are done and the data takes the form as per the requirement, we have to load the data to the destination systems.

All about Sql Server: Pivot Unpivot data in Sql server

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 ...

Normalization in sql server

  Database normalization is the process of efficiently organizing data in a database. There are two reasons
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.

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)

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.

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.

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,

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:

CUSTOMERS table:

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25),
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) );

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 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) );

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,

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:

CREATE TABLE CUSTMERORDERS( CUST_ID INT NOT NULL, ORDER_ID INT NOT NULL, SALE_DATE DATETIME, PRIMARY KEY
(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.

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) );

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:

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.

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.

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.

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.

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

Constraints in SQL

Hi,
                    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 that
can 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.

DEFAULT Constraint :

Provides a default value for a column when none is specified.

UNIQUE Constraint:

Ensures that all values in a column are different.

PRIMARY Key:

Uniquely identified each rows/records in a database table.

FOREIGN Key:

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.

INDEX:

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.

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

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) );

DEFAULT Constraint:

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.

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) );

UNIQUE Constraint:

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:

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).

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.

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) );

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).

Example:

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) );

ORDERS table:

CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(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.

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:

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) );

select Group_by Comma Separated

Hi ,
         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.

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