January 26, 2017 at 12:06 pm
I've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?
Here's some set-up script:
IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl;
CREATE TABLE #tbl
(
Id INT PRIMARY KEY
, C1 INT
, C2 INT
, C3 INT
, C4 INT
, C5 INT
, C6 INT
);
INSERT #tbl
(
Id
, C1
, C2
, C3
, C4
, C5
, C6
)
VALUES
(
1, 0, 1, 0, 1, 0, 0
)
,(
2, 0, 0, 0, 0, 0, 0
)
,(
3, 0, 1, 0, 1, 1, 1
);
SELECT *
FROM #tbl t;
I want to run an UPDATE query on this table, such that it ends up looking like this:
SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6);[/code]
Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 12:17 pm
Phil Parkin - Thursday, January 26, 2017 12:05 PMI've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?Here's some set-up script:
IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl;CREATE TABLE #tbl
(
Id INT PRIMARY KEY
, C1 INT
, C2 INT
, C3 INT
, C4 INT
, C5 INT
, C6 INT
);INSERT #tbl
(
Id
, C1
, C2
, C3
, C4
, C5
, C6
)
VALUES
(
1, 0, 1, 0, 1, 0, 0
)
,(
2, 0, 0, 0, 0, 0, 0
)
,(
3, 0, 1, 0, 1, 1, 1
);SELECT *
FROM #tbl t;
I want to run an UPDATE query on this table, such that it ends up looking like this:SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6);[/code]
Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.
UPDATE t #tbl
SET Ci=a.C1
, C2= a.C2
, C3 = a.C3
, C4 = a.C4
, C5 = a.C5
, C6 = a.C6
FROM (SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6)) a
JOIN #tbl t ON t.ID=a.id;
January 26, 2017 at 12:19 pm
Joe Torre - Thursday, January 26, 2017 12:17 PMPhil Parkin - Thursday, January 26, 2017 12:05 PMI've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?Here's some set-up script:
IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl;CREATE TABLE #tbl
(
Id INT PRIMARY KEY
, C1 INT
, C2 INT
, C3 INT
, C4 INT
, C5 INT
, C6 INT
);INSERT #tbl
(
Id
, C1
, C2
, C3
, C4
, C5
, C6
)
VALUES
(
1, 0, 1, 0, 1, 0, 0
)
,(
2, 0, 0, 0, 0, 0, 0
)
,(
3, 0, 1, 0, 1, 1, 1
);SELECT *
FROM #tbl t;
I want to run an UPDATE query on this table, such that it ends up looking like this:SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6);[/code]
Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.
UPDATE t
SET Ci=a.C1
, C2= a.C2
, C3 = a.C3
, C4 = a.C4
, C5 = a.C5
, C6 = a.C6
FROM (SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6)) a
JOIN #tbl t ON t.ID=a.id;
January 26, 2017 at 12:24 pm
Joe Torre - Thursday, January 26, 2017 12:19 PMJoe Torre - Thursday, January 26, 2017 12:17 PMPhil Parkin - Thursday, January 26, 2017 12:05 PMI've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?Here's some set-up script:
IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl;CREATE TABLE #tbl
(
Id INT PRIMARY KEY
, C1 INT
, C2 INT
, C3 INT
, C4 INT
, C5 INT
, C6 INT
);INSERT #tbl
(
Id
, C1
, C2
, C3
, C4
, C5
, C6
)
VALUES
(
1, 0, 1, 0, 1, 0, 0
)
,(
2, 0, 0, 0, 0, 0, 0
)
,(
3, 0, 1, 0, 1, 1, 1
);SELECT *
FROM #tbl t;
I want to run an UPDATE query on this table, such that it ends up looking like this:SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6);[/code]
Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.
UPDATE t
SET Ci=a.C1
, C2= a.C2
, C3 = a.C3
, C4 = a.C4
, C5 = a.C5
, C6 = a.C6
FROM (SELECT *
FROM
(
VALUES
(1, 2, 4, NULL, NULL, NULL, NULL)
, (2, NULL, NULL, NULL, NULL, NULL, NULL)
, (3, 2, 4, 5, 6, NULL, NULL)
) Results (Id, C1, C2, C3, C4, C5, C6)) a
JOIN #tbl t ON t.ID=a.id;
You can't hard-code values in your UPDATE! I want an UPDATE statement which runs on the first version of the table and produces version 2.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 12:25 pm
How many columns are in the actual data set?
😎
January 26, 2017 at 12:39 pm
there may be an easier way to do this, but I got stuck trying to figure out how to shift the values leftwise:
WITH sortvals AS
(SELECT t.Id, x.val, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS colnum
FROM #tbl t
CROSS APPLY (SELECT t.C1 AS val UNION ALL SELECT t.C2*2 AS val UNION ALL SELECT t.C3*3 AS val UNION ALL SELECT t.C4*4 AS val UNION ALL SELECT t.C5*5 AS val UNION ALL SELECT t.C6*6 AS val) x
WHERE x.val > 0),
updatevals AS
(SELECT t.Id, MAX(CASE WHEN v.colnum = 1 THEN val END) AS C1,
MAX(CASE WHEN v.colnum = 2 THEN val END) AS C2,
MAX(CASE WHEN v.colnum = 3 THEN val END) AS C3,
MAX(CASE WHEN v.colnum = 4 THEN val END) AS C4,
MAX(CASE WHEN v.colnum = 5 THEN val END) AS C5,
MAX(CASE WHEN v.colnum = 6 THEN val END) AS C6
FROM #tbl t
LEFT OUTER JOIN sortvals v ON t.Id = v.Id
GROUP BY t.Id)
UPDATE t SET
C1 = u.C1
C2 = u.C2
C3 = u.C3
C4 = u.C4
C5 = u.C5
C6 = u.C6
FROM #tbl t
INNER JOIN updatevals u ON t.Id = u.Id
January 26, 2017 at 12:43 pm
This is elementary my dear Phil, your illness must have been quite serious 😉
😎
;WITH BASE_DATA AS
(
SELECT
T.Id
,X.CV
,ROW_NUMBER() OVER
(
PARTITION BY T.Id
ORDER BY ISNULL(X.CV,999999999)
) AS OUTPOS
FROM #tbl T
CROSS APPLY
(
SELECT CASE WHEN T.C1 = 1 THEN 1 END UNION ALL
SELECT CASE WHEN T.C2 = 1 THEN 2 END UNION ALL
SELECT CASE WHEN T.C3 = 1 THEN 3 END UNION ALL
SELECT CASE WHEN T.C4 = 1 THEN 4 END UNION ALL
SELECT CASE WHEN T.C5 = 1 THEN 5 END UNION ALL
SELECT CASE WHEN T.C6 = 1 THEN 6 END
)X(CV)
)
SELECT
BD.Id
,MAX(CASE WHEN BD.OUTPOS = 1 THEN BD.CV END) AS CV1
,MAX(CASE WHEN BD.OUTPOS = 2 THEN BD.CV END) AS CV2
,MAX(CASE WHEN BD.OUTPOS = 3 THEN BD.CV END) AS CV3
,MAX(CASE WHEN BD.OUTPOS = 4 THEN BD.CV END) AS CV4
,MAX(CASE WHEN BD.OUTPOS = 5 THEN BD.CV END) AS CV5
,MAX(CASE WHEN BD.OUTPOS = 6 THEN BD.CV END) AS CV6
FROM BASE_DATA BD
GROUP BY BD.Id;
Output
January 26, 2017 at 12:47 pm
Eirikur Eiriksson - Thursday, January 26, 2017 12:25 PMHow many columns are in the actual data set?
😎
Six is the actual number.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 12:51 pm
Phil Parkin - Thursday, January 26, 2017 12:47 PMEirikur Eiriksson - Thursday, January 26, 2017 12:25 PMHow many columns are in the actual data set?
😎Six is the actual number.
Then you are good to go
😎
January 26, 2017 at 12:58 pm
Eirikur Eiriksson - Thursday, January 26, 2017 12:51 PMPhil Parkin - Thursday, January 26, 2017 12:47 PMEirikur Eiriksson - Thursday, January 26, 2017 12:25 PMHow many columns are in the actual data set?
😎Six is the actual number.
Then you are good to go
😎
Thanks, Eirikur
Leaving aside the slightly noxious beginnator, that's nice code. My brain's just not firing properly today. Thanks again.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 1:52 pm
I started looking at this before all the other solutions got posted, and I'm pretty sure the code I came up with is ugly, but I'd actually like to know just how inefficient it is, and where in the code those inefficiencies are. Explanations of why would also be appreciated. Trying to learn how to avoid problems....
CREATE TABLE #tbl (
Id int PRIMARY KEY
, C1 int
, C2 int
, C3 int
, C4 int
, C5 int
, C6 int
);
INSERT #tbl (Id, C1, C2, C3, C4, C5, C6)
VALUES (1, 0, 1, 0, 1, 0, 0),
(2, 0, 0, 0, 0, 0, 0),
(3, 0, 1, 0, 1, 1, 1);
DECLARE @LARGE_NUMBER AS int = 9999;
WITH CTE AS (
SELECT T.Id,
CAST(T.C1 AS char(1)) + ':' + CAST(T.C2 AS char(1)) + ':' + CAST(T.C3 AS char(1)) + ':' + CAST(T.C4 AS char(1)) + ':' +
CAST(T.C5 AS char(1)) + ':' + CAST(T.C6 AS char(1)) AS STRING,
T.C1, T.C2, T.C3, T.C4, T.C5, T.C6
FROM #tbl AS T
),
NUMBERS AS (
SELECT Id,
NULLIF(ItemNumber, @LARGE_NUMBER) AS ItemNumber,
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ItemNumber) AS RN
FROM (
SELECT C.Id, S.ItemNumber
FROM CTE AS C
CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
WHERE S.Item = 1
UNION ALL
SELECT C.Id, @LARGE_NUMBER AS ItemNumber
FROM CTE AS C
CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
WHERE S.Item = 0
) AS X
),
PIVOTED AS (
SELECT Id, [1] AS NEW_C1, [2] AS NEW_C2, [3] AS NEW_C3, [4] AS NEW_C4, [5] AS NEW_C5, [6] AS NEW_C6
FROM NUMBERS
PIVOT (MAX(ItemNumber) FOR RN IN ([1],[2],[3],[4],[5],[6])) AS PVT
)
UPDATE T
SET T.C1 = P.NEW_C1,
T.C2 = P.NEW_C2,
T.C3 = P.NEW_C3,
T.C4 = P.NEW_C4,
T.C5 = P.NEW_C5,
T.C6 = P.NEW_C6
FROM #tbl AS T
INNER JOIN PIVOTED AS P
ON T.Id = P.Id;
SELECT *
FROM #tbl
ORDER BY Id;
DROP TABLE #tbl;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 26, 2017 at 4:47 pm
sgmunson - Thursday, January 26, 2017 1:52 PMI started looking at this before all the other solutions got posted, and I'm pretty sure the code I came up with is ugly, but I'd actually like to know just how inefficient it is, and where in the code those inefficiencies are. Explanations of why would also be appreciated. Trying to learn how to avoid problems....
CREATE TABLE #tbl (
Id int PRIMARY KEY
, C1 int
, C2 int
, C3 int
, C4 int
, C5 int
, C6 int
);
INSERT #tbl (Id, C1, C2, C3, C4, C5, C6)
VALUES (1, 0, 1, 0, 1, 0, 0),
(2, 0, 0, 0, 0, 0, 0),
(3, 0, 1, 0, 1, 1, 1);DECLARE @LARGE_NUMBER AS int = 9999;
WITH CTE AS (
SELECT T.Id,
CAST(T.C1 AS char(1)) + ':' + CAST(T.C2 AS char(1)) + ':' + CAST(T.C3 AS char(1)) + ':' + CAST(T.C4 AS char(1)) + ':' +
CAST(T.C5 AS char(1)) + ':' + CAST(T.C6 AS char(1)) AS STRING,
T.C1, T.C2, T.C3, T.C4, T.C5, T.C6
FROM #tbl AS T
),
NUMBERS AS (SELECT Id,
NULLIF(ItemNumber, @LARGE_NUMBER) AS ItemNumber,
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ItemNumber) AS RN
FROM (
SELECT C.Id, S.ItemNumber
FROM CTE AS C
CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
WHERE S.Item = 1
UNION ALL
SELECT C.Id, @LARGE_NUMBER AS ItemNumber
FROM CTE AS C
CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
WHERE S.Item = 0
) AS X
),
PIVOTED AS (
SELECT Id, [1] AS NEW_C1, [2] AS NEW_C2, [3] AS NEW_C3, [4] AS NEW_C4, [5] AS NEW_C5, [6] AS NEW_C6
FROM NUMBERS
PIVOT (MAX(ItemNumber) FOR RN IN ([1],[2],[3],[4],[5],[6])) AS PVT
)
UPDATE T
SET T.C1 = P.NEW_C1,
T.C2 = P.NEW_C2,
T.C3 = P.NEW_C3,
T.C4 = P.NEW_C4,
T.C5 = P.NEW_C5,
T.C6 = P.NEW_C6
FROM #tbl AS T
INNER JOIN PIVOTED AS P
ON T.Id = P.Id;SELECT *
FROM #tbl
ORDER BY Id;DROP TABLE #tbl;
On this small data set the difference between the row_number method I used and yours is substantial and likely to increase if the size of the set grows.
😎
The IO stats give a hintEirikur
Table 'Worktable'. Scan count 0, logical reads 0
Table '#tbl'. Scan count 1, logical reads 2
sgmunson
Table '#tbl'. Scan count 2, logical reads 10
I'll have a look at the execution plan when I have the time.
January 27, 2017 at 1:05 am
Phil Parkin - Thursday, January 26, 2017 12:58 PMEirikur Eiriksson - Thursday, January 26, 2017 12:51 PMPhil Parkin - Thursday, January 26, 2017 12:47 PMEirikur Eiriksson - Thursday, January 26, 2017 12:25 PMHow many columns are in the actual data set?
😎Six is the actual number.
Then you are good to go
😎Thanks, Eirikur
Leaving aside the slightly noxious beginnator, that's nice code. My brain's just not firing properly today. Thanks again.
You are very welcome Phil.
My code is normally torn between the beginnator and the terminator 😀
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply