T SQL | SQL with Manoj

Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum

September 22, 2010 Leave a comment

–> Question:

I need maximum mark scored each students and min mark scored each students.

I have done this way…

select ID, max(mark), min(mark) 
from table1 
group by ID

But i need more field as Class…. I don’t want include class field in group by clause

–> My Answer:

Try joining the query with the table, like:

select T1.*, T2.ID, T2.max_mark, T2.min_mark
from table1 T1
join (select ID,max(mark) as max_mark,min(mark) as min_mark
	from table1 
	group by ID
) AS T2
on T1.ID=T2.ID

–> shaahs Answer:

In this situation you can use the window functions:

select ID, Class, 
	max(mark) over(partition by ID), 
	min(mark) over(Partition by ID) 
from table1

… in this cause you don’t want to include the fields in group by clause.

Categories: SQL Server Questions, SQL Tips Tags: MSDN TSQL forum, Window Functions

Combine multiple ROWS to CSV String… and vice-versa

September 16, 2010 16 comments

There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.

Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.

It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.

But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.

CREATE TABLE #tempCityState (State VARCHAR(5), City VARCHAR(50))

INSERT INTO #tempCityState
SELECT 'CO', 'Denver' UNION
SELECT 'CO', 'Teluride' UNION
SELECT 'CO', 'Vail' UNION
SELECT 'CO', 'Aspen' UNION
SELECT 'CA', 'Los Angeles' UNION
SELECT 'CA', 'Hanford' UNION
SELECT 'CA', 'Fremont' UNION
SELECT 'WA', 'Seattle' UNION
SELECT 'WA', 'Redmond' UNION
SELECT 'WA', 'Bellvue'

SELECT * FROM #tempCityState

State	City
CA	Hanford
CA	Fremont
CA	Los Angeles
CO	Denver
CO	Aspen
CO	Vail
CO	Teluride
WA	Seattle
WA	Redmond
WA	Bellvue

Using the “FOR XML PATH” syntax:

SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),2,200000)) AS Cities
INTO #tempCityStateCSV
FROM #tempCityState t

-- OR --

SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),1,1,'')) AS Cities
FROM #tempCityState t

SELECT * FROM #tempCityStateCSV

State	Cities
CA	Hanford,Fremont,Los Angeles
CO	Denver,Aspen,Vail,Teluride
WA	Seattle,Redmond,Bellvue

Now, converting it back to multiple rows:

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
	FROM  #tempCityStateCSV) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2

State	City
CA	Fremont
CA	Hanford
CA	Los Angeles
CO	Aspen
CO	Denver
CO	Teluride
CO	Vail
WA	Bellvue
WA	Redmond
WA	Seattle

Categories: SQL Tips Tags: CSV String, FOR XML PATH, ROWS to CSV, XML

Convert multiple Rows into a single column

August 17, 2010 2 comments

Just replied an answer on a SQL forum (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148860), so thought to post this as well for future reference.

How will you output the following record set:
123456788  City     399.99
123456788  County   499.99
123456788  Flood    299.99
123456788  Hazzard  199.99
123456789  City     333.99
123456789  County   444.99
123456789  Flood    222.99
123456789  Hazzard  111.99

... into following pattern:

id            Hazzard     Flood      City       County
123456788     199.99      299.99     399.99     499.99
123456789     111.99      222.99     333.99     444.99

The following query will do it and convert the rows into columns:

select 123456788 as id,  'Hazzard' as des, 199.99 as val
into #tempTable
union
select 123456788, 'Flood', 299.99 union
select 123456788, 'City', 399.99 union
select 123456788, 'County', 499.99 union
select 123456789, 'Hazzard', 111.99 union
select 123456789, 'Flood', 222.99 union
select 123456789, 'City', 333.99 union
select 123456789, 'County', 444.99
select * from #tempTable

select a.id as id, a.val as Hazzard, b.val as Flood, c.val as City, d.val as County
from #tempTable a,#tempTable b,#tempTable c, #tempTable d
where a.id=b.id and b.id=c.id and c.id=d.id
and a.des='Hazzard' and b.des='Flood' and c.des='City' and d.des='County'

drop table #tempTable

The above select code is with a simple old style where clause with multiple tables joining. The code could be also converted to self-joins.

Categories: JOINS, SQL Tips Tags: Convert Rows to Columns, SELF JOIN

Table variables are not stored in memory, but in tempdb

July 20, 2010 2 comments

Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.

–> To prove this I executed following code:

-- Batch #1
SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #2
CREATE TABLE #tempTab (j INT)
INSERT INTO #tempTab VALUES (1)
SELECT * FROM #tempTab

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #3
DECLARE @tabVar TABLE (i INT)
INSERT INTO @tabVar VALUES (1)
SELECT * FROM @tabVar

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

DROP TABLE #tempTab
GO

Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.

Batch #2: The second batch of stmt gives me 1 row with following table name:

#tempTab_____________________________________________________________000000019D39

Batch #3: and the third batch of stmt gives me 2 rows with following table name:

#tempTab_____________________________________________________________000000019D39
#0C4F413A

This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.

–> Check the full demo here:

Categories: Misconception, tempdb Tags: Table Variables, Temp Tables, tempdb, Temporary Tables

SQL Basics – Backup and Restore Database in SQL Server

June 5, 2010 1 comment

–> SQL Script to take Backup of a database:

USE [master]
GO

BACKUP DATABASE [AdventureWorks2014]
	TO  DISK = N'D:\SQL\AdventureWorks2014.bak' WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10
GO

–> SQL Script to Restore a Backup file:

USE [master]
GO

RESTORE DATABASE [TestManDB2] FROM  DISK = N'D:\SQL\TestManDB.bak' WITH FILE = 1,  
	MOVE N'TestManDB' TO N'D:\MSSQL\DATA\TestManDB2.mdf',  
	MOVE N'TestManDB_log' TO N'D:\MSSQL\DATA\TestManDB2_log.ldf',  
	NOUNLOAD,  
	STATS = 5
GO

–> Video on how to backup and restore a database: