I had a situation where I had to write out to a file where the column3 ( string) should be split and write to the next line if the length was greater than 50 . I used Tally table to do that.
WITH cteTally AS
(
SELECT *
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(v)
)
,cte AS
(
SELECT
v = (ROW_NUMBER() OVER(ORDER BY c1.v) * 50 + 1) - 50
FROM cteTally c1
CROSS JOIN cteTally c2
)
SELECT
tbl1.col1 'Column1'
,tbl1.col2 'Column2'
,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) 'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''
Explanation ---
select * from cteTally
V
-----
1
1
1
1
1
1
1
1
1
1
Select * from cte
v
----
1
51
101
151
201
.
.
.
.
4951
(100 rows because of cross join)
finally use the place holder row value in the sub string
SELECT
tbl1.col1 'Column1'
,tbl1.col2 'Column2'
,SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) 'Column3'
FROM tbl1
CROSS JOIN cte c
WHERE
SUBSTRING(rtrim(ltrim(tbl1.Column3)),c.v,50) <> ''
Another easy to understand example that I found on http://blogs.inkeysolutions.com/2011/05/creating-tally-tables-using-cte-in-sql.html
Generating a sequence of numbers from 1 to 20
DECLARE @Max AS INT = 20
;WITH CTE AS (
SELECT 1 Num
UNION ALL
SELECT Num + 1 FROM CTE WHERE Num < @Max
)
SELECT * FROM CTE
----Generating a sequence of Dates starting with the current date & going till next 20 days
DECLARE @MaxDate AS DATETIME = GETDATE() + 20
;WITH CTE AS (
SELECT GETDATE() Dates
UNION ALL
SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)
SELECT * FROM CTE
---Generating a sequence of Dates starting with the current date & going till next 20 days
DECLARE @MaxDate AS DATETIME = GETDATE() + 20
;WITH CTE AS (
SELECT GETDATE() Dates
UNION ALL
SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate
)
SELECT * FROM CTE
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label CTE. Show all posts
Showing posts with label CTE. Show all posts
Friday, November 30, 2018
Friday, July 20, 2012
Deleting duplicate rows
A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008
Initially the table had data
updateId ptno
1 95689
2 95689
3 95689
4 91458
5 91000
Result desired
1 95689
4 91458
5 91000
(3 unique rows)
SELECT UpdateID,Ptno INTO #TempPtno From dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;
/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Initially the table had data
updateId ptno
1 95689
2 95689
3 95689
4 91458
5 91000
Result desired
1 95689
4 91458
5 91000
(3 unique rows)
SELECT UpdateID,Ptno INTO #TempPtno From dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;
/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Subscribe to:
Posts (Atom)