Merge multiple rows into one row in a SQL Server

CREATE TABLE ProductsDemo
(
ID INT PRIMARY KEY,
Sno VARCHAR(100),
ProdType VARCHAR(100),
ProdQuantity Int
)
GO


INSERT INTO ProductsDemo
VALUES
(1, '1', 'Dell',2),
(2, '1', 'Lenovo',3) ,
(3, '1', 'Nokia',1),
(4, '1', 'Apple',4),
(5, '1', 'Samsung',1) ,
(6, '2', 'Microsoft',2) ,
(7, '2', 'Logitech',3)
GO

select * from ProductsDemo

 --Dynamic output
SELECT
    Sno,
    STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE b.Sno = a.Sno
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,'') AS ProdType
FROM
    (SELECT DISTINCT Sno FROM ProductsDemo ) AS a;
GO

----Update only ProdType

--UPDATE ProductsDemo SET
-- ProdType=
-- STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
--            FROM ProductsDemo  as b
--            WHERE  ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5
--            FOR XML PATH(''), TYPE
--            ).value('.', 'varchar(max)'
--         ),1, 1,'')
-- WHERE ID = 1

--select * from ProductsDemo

--UPDATE ProductsDemo SET ProdType=
-- STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
--            FROM ProductsDemo  as b
--            WHERE  ID = 6 or ID=7
--            FOR XML PATH(''), TYPE
--            ).value('.', 'varchar(max)'
--         ),1, 1,'')
-- WHERE ID = 6

--select * from ProductsDemo
---------------------------

--Update ProdType With quantity
select * from ProductsDemo
 --Dynamic output
SELECT
    Sno,
    STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE b.Sno = a.Sno
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,'') AS ProdType,
(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE c.Sno = a.Sno) as RollupQ
FROM
    (SELECT DISTINCT Sno FROM ProductsDemo ) AS a;
GO
select * from ProductsDemo

--Update actual table for ProdType and quantity
UPDATE ProductsDemo SET
ProdType=
 STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE  ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,''),
ProdQuantity=(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5)
WHERE ID = 1

 select * from ProductsDemo

UPDATE ProductsDemo SET ProdType=
 STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE  ID = 6 or ID=7
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,''),
ProdQuantity=(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE ID = 6 or ID=7 )
WHERE ID = 6


select * from ProductsDemo

delete from ProductsDemo where ID=7
delete from ProductsDemo where ID=2 or ID=3 or ID=4 or ID=5
select * from ProductsDemo

--delete ProductsDemo
--Drop table ProductsDemo

Note: Problem with above update query, it will merge duplicate ProdTypes, if u want to handle duplicate ProdTypes then change the query.

SQL Server Management Studio path

SQL Server Management Studio path
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2014

How to find SQL Sessions

sp_who2

I have 300K records in SQL, out of that 1k records are having wrong data in one column. How you are going to fix it.

Try this...You may have better approach, but this is one approach.

1.Get right data from Source System to flat file with primary key value and column value.
EX: Employee Location is wrong, in this scenario, prepare a flat file with Epmid and EmpLocation.
2.Import above data in to same DB where your 300k records table is there
3.Now you are having 2 tables (One is actual and another one is imported table)
4.Now compare the 2 tables to find are there any data difference.
5.If you find data difference, now update actual table (300k) with imported table
6.Now compare the 2 tables to find are there any data difference, it should give 0 differences..

SQL table compare between 2 databases

SQL Server Data Tools for Visual Studio 2013 -Run as Administrator
TOOLS-->SQL Server-->New Data Comparison...
Click on New Connection..under Source Database
Select SQL Server Name and DB Name
Click on New Connection..under Target Database
Select SQL Server Name and DB Name
Click Next
Select Tables which you want to compare
Finish
You will see data difference b/w 2 DBs

Sql Querry to find all active DB connection

SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM    sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)

Remove “Restricted User” in SQL Server

Remove “Restricted User” in SQL Server
---------------------------------------------------------------
 
Use DatabaseName
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO

SQL ROW_NUMBER OVER AND PARTITION BY

Table Name: myTable
Columns:

-->roll_no
-->classId
-->location

SQL Statements

--Get the count of rows based on columns

  SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable order by rowCnt desc
 
--Get the count of rows based on columns using where clause

 SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable
    where myTable.roll_no='1001'
 
--Query sub query data based on where clause  

    SELECT rid FROM(
 SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable
    where myTable.roll_no='1001'
)X WHERE classId= 7 and upper(location) != 'USA' and rowCnt = 1

create database permission denied in database 'master'

First logout from SQL with current user
Login as SQL Administrator
Expand Server
Expand Security
Expand Logins
Now select the account name which you are getting create database permission denied in database 'master' error
Right click
Select Properties
You will see new window
Click on Server Roles
Click on sysadmin
Ok
Now you login as account name which you are getting

SQL Get All column names from All Tables

Why we need to create Named Query in SSAS instead of using Normal View

In Actual Development, You will not have any permission in Database (Client Database), so you can not create View in Database.
So we can do only using Named query from Data Source Views and named query will not be available in Actual Database. It will be available in Data Source Views level

Temp table in SQL

Query to trace long running queries executing in the background

select r.session_id
,status,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
as query_text   --- this is the statement executing right now,qt.dbid,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id;

SQL - Query Tuning Techniques

While dealing with performance problems, database professionals tend to focus on technical aspects of the system such as resource queues, resource utilization and so on.

SQL Server introduced a component called data collector that collects different sets of data related to performance and other from different sources and store it in a relational warehouse known as management data warehouse.

The data collector installs three system data collection sets that collect disk usage, server activity, and query statics information.

The data collection platform helps you automate the collection of performance and other information and also analyze it graphically with preconfigured  reports.

Metadata Management - Crawler Query - To get schema info from a list of servers

Please find further details on usage in my earlier posts and below is the code snippet to get schema information:

SET NOCOUNT ON
DECLARE @AllTables TABLE (
ServerName sysname,
DATABASE_NAME sysname,
TABLE_SCHEMA sysname )

DECLARE @SQLTemplate nvarchar(1500)
SET @SQLTemplate=
N' SELECT @@SERVERNAME AS Servername'
+ N', ''?'' AS DATABASE_NAME'
+ N', B.Name AS TABLE_SCHEMA FROM [?].sys.schemas B WHERE B.schema_id=1 OR B.schema_id BETWEEN 5 AND 16383;'        
  INSERT INTO @AllTables (ServerName ,
DATABASE_NAME,
TABLE_SCHEMA)

EXEC sp_msforeachdb @SQLTemplate

SET NOCOUNT OFF

SELECT * FROM @AllTables
WHERE DATABASE_NAME NOT IN ('master','model','msdb','tempdb')
ORDER BY DATABASE_NAME, TABLE_SCHEMA

How to create foreign key in SQL

Create Table Emp
(
EId Int primary key,
EName varchar(20)
)
Create Table Leavedetails
(
LId int,
NoOfDays int,
Emp_ID int foreign key references Emp(EId)
)
Group By
Select EName, Sum(NoOfDays) NoOfDays from Emp
Inner Join Leavedetails on Emp.EId=Leavedetails.Emp_ID
Group By EName
The column names after Select statement, should be member of Group By or Aggregate function (SUm, Avg, Count), other wise you will get error.

SQL stored procedures

To get all stored procedures

exec sp_stored_procedures

DatabaseName.dbo.sp_stored_procedures

DatabaseName.dbo.sp_helptext StoredProcedureName

to get all information
SELECT * FROM sys.objects;

SQL Server 2005 To get all stored procedures

SELECT * FROM sys.procedures;