Tuesday, March 3, 2009

How To Split A Comma Delimited String

In one of my previous posts I wrote about “How to Create a Comma Delimited List”.  Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id AllNames
1 A,B,C
2 A,B
3 X,Y,Z

And here is the expected output -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

Create Sample Data :

-- Create Table for  Sample Data
CREATE TABLE Test
(
ID INT,
AllNames VARCHAR(100)
)
GO
-- Load Sample Data
INSERT INTO test SELECT
1, 'A,B,C' UNION ALL SELECT
2, 'A,B'  UNION ALL SELECT
3, 'X,Y,Z'
GO

-- Verify the Sample Data
SELECT Id, AllNames
FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS

(
    SELECT
        id,
        CAST('<M>' + REPLACE( Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names
    FROM Test
)
SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS Names
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)

 

Acknowledgement : Well seriously don’t know.  Somewhere I came across this solution while answering / participating  on MSDN Sql Server Forums.  So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums.  I’m one of those freaks who hate to read books ;)

- Mangal Pardeshi

Posted by Unknown at Tuesday, March 03, 2009 22 comments
Labels: ,

Monday, February 23, 2009

Create A Comma Delimited List From a Column

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

And here is the expected output -

Id AllNames
1 A, B, C
2 A, B
3 X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table
CREATE TABLE Test
(
Id INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,'Z'
GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id 
           ,AllNames = SubString (( SELECT ', ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id
           ,AllNames = REPLACE (( SELECT T2.Names AS 'data()'
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), ' ', ', ')
FROM Test as T1
GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id 
           ,AllNames = SubString (( SELECT '| ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

Mangal Pardeshi

SQL MVP

Posted by Unknown at Monday, February 23, 2009 9 comments
Labels: ,

Friday, February 20, 2009

SQL Server Versions and Editions.

Lets see how to find which Sql Server Version is installed and Service Packs applied on your machine-

To know Execute

SELECT @@version

If you are on Sql Server 2000 or later use following which will give you a more elegant output -

SELECT SERVERPROPERTY('productversion') as VersioNumber
,SERVERPROPERTY ('productlevel') as SPLevel
,SERVERPROPERTY ('edition') as ServerEdition

And here is detailed chart which will show you SQL Server version numbers and the corresponding product or service pack level -

Sr NoVersion NumberReleaseSP Level
10.110.0.1600.22SQL Server 2008RTM
----
9.19.0.1399.00SQL Server 2005RTM
9.29.0.2047.00SQL Server 2005SP1
9.39.0.3042.00SQL Server 2005SP2
9.49.0.4035.00SQL Server 2005SP3
----
8.18.00.194.00SQL Server 2000RTM
8.28.00.384.00SQL Server 2000SP1
8.38.00.534.00SQL Server 2000SP2
8.48.00.760.00SQL Server 2000SP3
8.58.00.2039.00SQL Server 2000SP4
----
7.17.00.623.00SQL Server 7.0RTM
7.27.00.699.00SQL Server 7.0SP1
7.37.00.842.00SQL Server 7.0SP2
7.47.00.961.00SQL Server 7.0SP3
7.57.00.1063.00SQL Server 7.0SP4
----
6.516.50.201.00SQL Server 6.5RTM
6.526.50.213.00SQL Server 6.5SP1
6.536.50.240.00SQL Server 6.5SP2
6.546.50.258.00SQL Server 6.5SP3
6.556.50.281.00SQL Server 6.5SP4
6.566.50.415.00SQL Server 6.5SP5
6.576.50.416.00SQL Server 6.5SP5a
6.586.50.479.00SQL Server 6.5SP5a update
----
6.016.00.121.00SQL Server 6.0RTM
6.026.00.124.00SQL Server 6.0SP1
6.036.00.139.00SQL Server 6.0SP2
6.046.00.151.00SQL Server 6.0SP3

- Mangal Pardeshi

Posted by Unknown at Friday, February 20, 2009 1 comments
Labels: , ,

Excel 2007 Linked Server

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from 2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )
And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'. 

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
For Creating Linked Server from Management Studio:
Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No 
Again remember if you have column names in Excel then HDR=Yes.


- Mangal Pardeshi.

Posted by Unknown at Friday, February 20, 2009 3 comments
Labels: , , ,

Tuesday, February 3, 2009

Calculating Running Totals in SQL

 

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query.  So instead of using the technique I used in this post you should use technique mentioned in my new post -  How to Calculating Running Totals

In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -

  • Running Total for all records.
  • Running Total on each Group of Records.
  • Running Total on each Date.

Here is our sample table :

ShipId OrderId ShipDate Quantity
1 1 2009-02-01 12:06:16.820 10
2 1 2009-02-02 16:16:16.820 15
3 2 2009-02-01 13:26:16.820 20
4 2 2009-02-03 17:40:16.820 12
5 2 2009-02-04 13:05:16.820 15
6 3 2009-02-03 14:25:16.820 25
7 3 2009-02-04 15:50:16.820 50
8 3 2009-02-05 19:40:16.820 30
9 4 2009-02-06 15:30:16.820 20
10 4 2009-02-07 16:20:16.820 10
11 5 2009-02-05 17:05:16.820 35

Create Sample Data:

-- Create Table
CREATE TABLE Sales
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT )
GO
-- Load Sample Data

INSERT INTO Sales SELECT
1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT
2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT
3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT
4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT
5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT
6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT
7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT
8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT
9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT
10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT
11,5, 'Feb 05 2009 05:05:16:820PM',35


Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity

Here is the result returned by above query :
ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 45
4 2 2009-02-03 17:40:16.820 12 57
5 2 2009-02-04 13:05:16.820 15 72
6 3 2009-02-03 14:25:16.820 25 97
7 3 2009-02-04 15:50:16.820 50 147
8 3 2009-02-05 19:40:16.820 30 177
9 4 2009-02-06 15:30:16.820 20 197
10 4 2009-02-07 16:20:16.820 10 207
11 5 2009-02-05 17:05:16.820 35 242

Case II: Running Total on each Group of Records.

Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
AND A.OrderId = B.OrderId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY OrderId

And the Output :
ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 20
4 2 2009-02-03 17:40:16.820 12 32
5 2 2009-02-04 13:05:16.820 15 47
6 3 2009-02-03 14:25:16.820 25 25
7 3 2009-02-04 15:50:16.820 50 75
8 3 2009-02-05 19:40:16.820 30 105
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 30
11 5 2009-02-05 17:05:16.820 35 35

Case III: Running Total on each Date.

Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.shipDate >= B.Shipdate
AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0)
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY A.ShipDate

And the output :

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
3 2 2009-02-01 13:26:16.820 20 30
2 1 2009-02-02 16:16:16.820 15 15
6 3 2009-02-03 14:25:16.820 25 25
4 2 2009-02-03 17:40:16.820 12 37
5 2 2009-02-04 13:05:16.820 15 15
7 3 2009-02-04 15:50:16.820 50 65
11 5 2009-02-05 17:05:16.820 35 35
8 3 2009-02-05 19:40:16.820 30 65
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 10

Hopefully these examples will help you understand different methods that can be used to calculate running totals.

- Mangal Pardeshi

Posted by Unknown at Tuesday, February 03, 2009 2 comments
Labels: , ,
Subscribe to: Posts (Atom)