July 15, 2015 at 11:53 am
Hi All,
I have four columns in my table, the first one is the identity column
col1 Col1 col2 col3
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
when, I see, @@@ sign in my col4, I need to restart the col3 from 1 again so it will look like this
col1 Col2 col3 col4
1 12 1 This is Test1
2 12 2 This is Test1
3 12 3 This is Test3
4 12 4 This is Test4
5 12 5 @@@@@
6 12 1 This is another test1
7 12 2 This is another Test2
Is it possible to do that? Any help will be appreciated.
July 15, 2015 at 12:39 pm
Do you need to do it at insert or is it an update AFTER the rows are inserted?
It is possible to do it in both cases. Seems like an odd requirement though and perhaps a better design is possible.
Here's a way to do it in insert:
USE test;
GO
DECLARE @t TABLE
(
col1 INT IDENTITY(1, 1),
col2 TINYINT,
col3 TINYINT,
col4 VARCHAR(50)
);
INSERT INTO @t
(col2, col3, col4)
VALUES
(12, 1, 'This is Test1'),
(12, 2, 'This is Test1'),
(12, 3, 'This is Test3'),
(12, 4, 'This is Test4'),
(12, 5, '@@@@@');
WITH test
AS (
SELECT
ROW_NUMBER() OVER (ORDER BY col1 DESC) AS RowNO,
col1
FROM
@t AS T
)
INSERT INTO @t
(
col2,
col3,
col4
)
SELECT
12,
CASE WHEN T.col4 = '@@@@@' THEN 1
ELSE col3 + 1
END,
'This is another test1'
FROM
@t AS T
JOIN test
ON T.col1 = test.col1
WHERE
test.RowNO = 1;
SELECT
*
FROM
@t AS T;
WITH test
AS (
SELECT
ROW_NUMBER() OVER (ORDER BY col1 DESC) AS RowNO,
col1
FROM
@t AS T
)
INSERT INTO @t
(
col2,
col3,
col4
)
SELECT
12,
CASE WHEN T.col4 = '@@@@@' THEN 1
ELSE col3 + 1
END,
'This is another test2'
FROM
@t AS T
JOIN test
ON T.col1 = test.col1
WHERE
test.RowNO = 1;
SELECT
*
FROM
@t AS T;
This may not be the best way but it is one way to do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2015 at 12:41 pm
What about something like this assuming no gaps in the identity,
CREATE TABLE #test(COL_ONE int identity, COL_TWO varchar(30))
INSERT INTO #test(COL_TWO)
SELECT 'dfgdfg'
UNION ALL
SELECT 'dfghdgfh'
UNION ALL
SELECT 'dghk'
UNION ALL
SELECT 'rtyurtyurtyu'
UNION ALL
SELECT '@@@@@'
UNION ALL
SELECT 'dsdfgsd'
UNION ALL
SELECT 'ddgfhdgh'
UNION ALL
SELECT '@@@@@'
UNION ALL
SELECT 'vzxcvxcv'
UNION ALL
SELECT 'uiyiurtyy'
WITH TEMP_CTE_ONE AS(
SELECT TEST_ONE.COL_ONE TEST_ONE_COL_ONE, TEST_ONE.COL_TWO TEST_ONE_COL_TWO, TEST_TWO.COL_ONE TEST_TWO_COL_ONE, TEST_TWO.COL_TWO TEST_TWO_COL_TWO FROM #test TEST_ONE
LEFT OUTER JOIN #test TEST_TWO ON TEST_ONE.COL_ONE = TEST_TWO.COL_ONE - 1 AND TEST_TWO.COL_TWO != '@@@@@'
), TEMP_CTE_TWO AS(
SELECT TEST_ONE_COL_ONE PAR_COL_ONE, TEST_ONE_COL_ONE CUR_COL_ONE, TEST_ONE_COL_TWO COL_VALUE FROM TEMP_CTE_ONE WHERE TEST_TWO_COL_ONE IS NULL
UNION ALL
SELECT TEMP_CTE_TWO.PAR_COL_ONE, TEST_THREE.COL_ONE, TEST_THREE.COL_TWO FROM
#test TEST_THREE, TEMP_CTE_TWO
WHERE
TEST_THREE.COL_ONE = TEMP_CTE_TWO.CUR_COL_ONE - 1 AND TEST_THREE.COL_TWO != '@@@@@'
)
SELECT CUR_COL_ONE, COL_VALUE, ROW_NUMBER() OVER(PARTITION BY PAR_COL_ONE ORDER BY CUR_COL_ONE) FROM TEMP_CTE_TWO
ORDER BY PAR_COL_ONE, CUR_COL_ONE
that gets you output,
CUR_COL_ONECOL_VALUESEQ_NUMBER
1dfgdfg1
2dfghdgfh2
3dghk3
4rtyurtyurtyu4
6dsdfgsd1
7ddgfhdgh2
9vzxcvxcv1
10uiyiurtyy2
July 15, 2015 at 1:00 pm
But you aren't returning the '@@@@@' columns that are in the set.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2015 at 1:07 pm
If needed change the select to 😀
SELECT CUR_COL_ONE, COL_VALUE, ROW_NUMBER() OVER(PARTITION BY PAR_COL_ONE ORDER BY CUR_COL_ONE) AS SEQ_NUMBER FROM TEMP_CTE_TWO
UNION ALL
SELECT COL_ONE, COL_TWO, 0 FROM #test WHERE COL_TWO = '@@@@@'
ORDER BY CUR_COL_ONE
July 15, 2015 at 1:36 pm
ZZartin (7/15/2015)
If needed change the select to 😀
SELECT CUR_COL_ONE, COL_VALUE, ROW_NUMBER() OVER(PARTITION BY PAR_COL_ONE ORDER BY CUR_COL_ONE) AS SEQ_NUMBER FROM TEMP_CTE_TWOUNION ALL
SELECT COL_ONE, COL_TWO, 0 FROM #test WHERE COL_TWO = '@@@@@'
ORDER BY CUR_COL_ONE
But in the sample provided by the OP the '@@@@@' were not 0's they were the next number in the group.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2015 at 2:02 pm
Ah yes I was looking at the number on the end of the strings 😛 This should do it.
WITH TEMP_CTE_ONE AS(
SELECT TEST_ONE.COL_ONE TEST_ONE_COL_ONE, TEST_ONE.COL_TWO TEST_ONE_COL_TWO, TEST_TWO.COL_ONE TEST_TWO_COL_ONE, TEST_TWO.COL_TWO TEST_TWO_COL_TWO FROM #test TEST_ONE
LEFT OUTER JOIN #test TEST_TWO ON TEST_ONE.COL_ONE = TEST_TWO.COL_ONE - 1
), TEMP_CTE_TWO AS(
SELECT TEST_ONE_COL_ONE PAR_COL_ONE, TEST_ONE_COL_ONE CUR_COL_ONE, TEST_ONE_COL_TWO COL_VALUE FROM TEMP_CTE_ONE WHERE TEST_ONE_COL_TWO = '@@@@@' OR TEST_TWO_COL_TWO IS NULL
UNION ALL
SELECT TEMP_CTE_TWO.PAR_COL_ONE, TEST_THREE.COL_ONE, TEST_THREE.COL_TWO FROM
#test TEST_THREE, TEMP_CTE_TWO
WHERE
TEST_THREE.COL_ONE = TEMP_CTE_TWO.CUR_COL_ONE - 1 AND TEST_THREE.COL_TWO != '@@@@@'
)
SELECT CUR_COL_ONE, COL_VALUE, ROW_NUMBER() OVER(PARTITION BY PAR_COL_ONE ORDER BY CUR_COL_ONE) AS SEQ_NUMBER FROM TEMP_CTE_TWO
ORDER BY PAR_COL_ONE, CUR_COL_ONE
July 15, 2015 at 2:56 pm
This is essentially an islands and gaps problem with the added wrinkle that the '@@@@@' records are counted in both the islands and the gaps. I used a cross apply to add a column and row to differentiate when these records are acting as an island and a gap.
DECLARE @tbl TABLE (col1 int, col2 int, col3 int, col4 varchar(30))
INSERT @tbl(col1, col2, col3, col4)
VALUES(1, 12, 1, 'This is Test1'),
(2, 12, 2, 'This is Test1'),
(3, 12, 3, 'This is Test3'),
(4, 12, 4, 'This is Test4'),
(5, 12, 5, '@@@@@'),
(6, 12, 1, 'This is another test1'),
(7, 12, 2, 'This is another Test2')
SELECT *
FROM @tbl
;
WITH groups AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY col1, is_boundry)
-ROW_NUMBER() OVER(PARTITION BY is_boundry ORDER BY col1) AS grp
FROM @tbl
CROSS APPLY (
SELECT CAST(0 AS BIT) AS is_boundry
UNION
SELECT CAST(1 AS BIT)
WHERE col4 = '@@@@@'
) AS c
)
SELECT COL1, COL2, col3, col4, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY col1)
FROM groups
WHERE is_boundry <> 1
ORDER BY col1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2015 at 3:02 pm
drew.allen (7/15/2015)
This is essentially an islands and gaps problem with the added wrinkle that the '@@@@@' records are counted in both the islands and the gaps. I used a cross apply to add a column and row to differentiate when these records are acting as an island and a gap.
DECLARE @tbl TABLE (col1 int, col2 int, col3 int, col4 varchar(30))
INSERT @tbl(col1, col2, col3, col4)
VALUES(1, 12, 1, 'This is Test1'),
(2, 12, 2, 'This is Test1'),
(3, 12, 3, 'This is Test3'),
(4, 12, 4, 'This is Test4'),
(5, 12, 5, '@@@@@'),
(6, 12, 1, 'This is another test1'),
(7, 12, 2, 'This is another Test2')
SELECT *
FROM @tbl
;
WITH groups AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY col1, is_boundry)
-ROW_NUMBER() OVER(PARTITION BY is_boundry ORDER BY col1) AS grp
FROM @tbl
CROSS APPLY (
SELECT CAST(0 AS BIT) AS is_boundry
UNION
SELECT CAST(1 AS BIT)
WHERE col4 = '@@@@@'
) AS c
)
SELECT COL1, COL2, col3, col4, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY col1)
FROM groups
WHERE is_boundry <> 1
ORDER BY col1
Drew
Like it. I knew there was better solution than mine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply