April 23, 2010 at 12:11 am
Hi Guys,
I have a column in a table that I have stored in the format;
--/---/--/--
e.g 01/345/76/98
But I need to change it to the format;
--/---/---/--
e.g 01/345/076/98, this is to say, I need to replace the second '/' at position 7 with '/0'.
This will increase the length of the column to 13, and I have allowed for this.
I have tested with stuff as below;
SELECT STUFF(IndUniqId, 7, 1, '/0')from form5Test
the name of the column is IndUniqId
this works pretty fine as I want except it doesn't write the values, but only selects them and when I go back to the table, I find them as Original.
Does anyone have an idea how to go around this?
Many thanks
April 23, 2010 at 12:23 am
You will have to run an UPDATE query like below
UPDATE form5Test
SET IndUniqId = STUFF(IndUniqId, 7, 1, '/0')
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 23, 2010 at 1:49 am
Thanks Kingstone
April 23, 2010 at 7:35 pm
mugahjoseph (4/23/2010)
Hi Guys,I have a column in a table that I have stored in the format;
--/---/--/--
e.g 01/345/76/98
But I need to change it to the format;
--/---/---/--
e.g 01/345/076/98, this is to say, I need to replace the second '/' at position 7 with '/0'.
This will increase the length of the column to 13, and I have allowed for this.
I have tested with stuff as below;
SELECT STUFF(IndUniqId, 7, 1, '/0')from form5Test
the name of the column is IndUniqId
this works pretty fine as I want except it doesn't write the values, but only selects them and when I go back to the table, I find them as Original.
Does anyone have an idea how to go around this?
Many thanks
As a side bar, I wouldn't store data in a denormalized form like that. Instead, I'd store the data in 4 separate columns so that indexes actually mean something and you don't have to parse or use SUBSTRING to execute simple inqueries.
--Jeff Moden
June 7, 2011 at 6:24 pm
Kingston,
Would you be able to help me with the following code utilizing SQL SERVER STUFF function.
I need to acquire the total number of beds in a vertically placement like the following:
0776,Hospital,201106072001,12,26,0,0,9,0,1,0,0,0
I used the following code to create the above test data:
stuff (( select ',' +
cast(sum(case
when placement in ('1','2','5','7') then 1
else 0
end) as varchar)
FROM @CENSUS_WORK
group by (placement)
for XML PATH('')
),1,0,'')
However the state does not want the 0 instead they would like me to put a ',' where our site currently does not have a certain
bed category which is displayed above with a 0.
I attempted to update the code above to:
stuff (( select ',' +
cast(sum(case
when placement = '1' then 1
when placement = '2' then 1
when placement = '3' then ','+convert(varchar(1),placement)
when placement = '5' then 1
when placement = '6' then ','+convert(varchar(1),placement)
when placement = '7' then 1
when placement = '8' then ','+convert(varchar(1),placement)
when placement = '9' then ','+convert(varchar(1),placement)
when placement = '10' then ','+convert(varchar(1),placement)
end) as varchar)
FROM @CENSUS_WORK
group by (placement)
for XML PATH('')
),1,0,'')
and receive the following ERROR:
Msg 245, Level 16, State 1, Procedure usp_nh_HAvBed, Line 179
Conversion failed when converting the varchar value ',3' to data type int.
I am aware that the problem is occurring due to the different data type of data field [placement] int and (,) varchar, however I am not sure how to go about updating the 0 values to a ',' utilizing stuff function and the aggregate function of sum. Do I need to add another function
to convert the int to varchar besides what I have above.
Thank you
June 8, 2011 at 4:30 am
Hello,
As I understand you want to replace ',0' by ',', right?.
Try it,
SELECT ',' + CASE
WHEN TotalPlacement = 0 then ''
WHEN Placement IN (1, 2, 5, 7) THEN CAST(TotalPlacement AS VARCHAR(9))
ELSE ''
END
FROM (
SELECT Placement, COUNT(*) AS TotalPlacement
FROM @CENSUS_WORK
group by Placement
) X
for XML PATH('')
I didn't try it but this instruction, or something like, could work.
Regards,
Francesc
June 8, 2011 at 6:44 am
Thank you For you reply, Code worked great except for the following problem:
Code you provided produced the following results,
,18,22,,,12,,1,,,
In which I was just missing one comma at the end. There is 10 bed categories and the "1" in the result set above represents bed category placement 7. The file specifications state that I should have 7,,,,
Placement 7 = 7,
Placement 8 = ,
placement 9 = ,
placement 10 = ,
Any Idea what I am missing from the code below.
Also,
I had to update the code to test for a specific placement values due to the fact if Placement 1
contains a 0 I need to display the zero value. I only want the comma for specific placement values.
The updated code is below.
SELECT
',' + CASE
WHEN placement IN (1, 2, 5, 7) THEN CAST(TotalPlacement AS VARCHAR(9))
WHEN placement IN (3, 4, 6, 8, 9, 10) and TotalPlacement = 0 then ','
END
FROM (
SELECT placement, COUNT(*) AS TotalPlacement
FROM Raw_Data$
group by placement
) X
for XML PATH('')
June 8, 2011 at 8:20 am
Hello,
well, that's I understand from your post:
- For placements 1, 2, 5, 7 you want to show the count number (any value including zero),
- for other placements you want to show the empty chain,
- all those values are separated by comma.
These conditions are coded in this SELECT clause,
SELECT
',' + CASE
WHEN placement IN (1, 2, 5, 7) THEN CAST(TotalPlacement AS VARCHAR(9))
ELSE ''
END
About the commas I'm not sure about your specifications. If you want a comma at begin but no comma at end, that SELECT clause is Ok; if you want a comma at end but not at begin you should change " ',' + CASE..." by "CASE... + ',' ". And if you want both commas, so ten values delimited by eleven commas, maybe this dirty trick in the FROM clause will help you:
FROM (
SELECT placement, COUNT(*) AS TotalPlacement
FROM Raw_Data$
group by placement
UNION SELECT 99, 0
) X
Hope this helps,
Francesc
June 8, 2011 at 8:41 am
Hello,
First I would like to thank you for you assistance.
You are correct on the following,
- For placements 1, 2, 5, 7 I want to show the count number (any value including zero)
- For other placements I want to show the empty chain ","
I tested the following code in which everything looks good (including comma placement) the only problem is Placement "7" contains no data so i expect to see a 0 and it is not.
results from code below:
0776,Hospital,201106081032,10,16,,,8,,,,,
Needs to be:
0776,Hospital,201106081032,10,16,,,8,0,,,
stuff ((
SELECT ',' + CASE
WHEN placement IN (1, 2, 5, 7) THEN CAST(TotalPlacement AS VARCHAR(9))
ELSE ''
END
FROM (
SELECT placement, COUNT(*) AS TotalPlacement
FROM havbed$
group by placement
) X
for XML PATH('')
),1,0,'')
June 9, 2011 at 2:18 am
Hello again,
executing this code,
select stuff ((
SELECT ',' + CASE
WHEN placement IN (1, 2, 5, 7) THEN CAST(TotalPlacement AS VARCHAR(9))
ELSE ''
END
FROM (
SELECT 1 as placement, 10 AS TotalPlacement
UNION SELECT 2, 16
UNION SELECT 3, 1000
UNION SELECT 4, 1000
UNION SELECT 5, 8
UNION SELECT 6, 1000
UNION SELECT 7, 0
UNION SELECT 8, 1000
UNION SELECT 9, 1000
UNION SELECT 10, 1000
) X
for XML PATH('')
),1,1,'')
(I changed a bit the last line to eliminate the initial coma) I obtain this result:
10,16,,,8,,0,,,
Maybe the problem is within this SELECT obtaining the totals,
SELECT placement, COUNT(*) AS TotalPlacement
FROM havbed$
group by placement
Could you verify if it returns any value for placement 7?. If there is no value for placement 7 (or 6, or 5, or...) there is nothing to concatenate. In this case you can still force a result for any missing value with another dirty trick, I tried it:
SELECT OHNO.Placement, ISNULL(TotalPlacement, 0) AS TotalPlacement
FROM (SELECT 1 AS Placement UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) OHNO
LEFT JOIN (
SELECT 1 as placement, 10 AS TotalPlacement
UNION SELECT 2, 16
UNION SELECT 3, 1000
-- UNION SELECT 4, 1000
-- UNION SELECT 5, 8
-- UNION SELECT 6, 1000
-- UNION SELECT 7, 0
UNION SELECT 8, 1000
UNION SELECT 9, 1000
UNION SELECT 10, 1000
) X
ON OHNO.Placement = X.Placement
Regards,
Francesc
June 9, 2011 at 7:40 am
Hello,
I am in agreement with you that the problem resides in the select statement. I am unsure on where to include the actual table Havbed with the code you provide me to confirm a 0 value for placement
7. The code below is just returning a constant number I need the calculations based off the data in the
table Havbed. Thank you again for your help. Do you think it would be better to create 10
variables to hold the values for each placement and then utilize the stuff function to return the
values vertically.
June 9, 2011 at 9:09 am
the problem resides in the select statement
That's true?, nice, now the problem is located and you can fix it.
I am unsure on where to include the actual table Havbed with the code you provide me
The resultant query will be
SELECT P.Placement, ISNULL(TotalPlacement, 0) AS TotalPlacement
FROM Placements P
LEFT JOIN (
SELECT placement, COUNT(*) AS TotalPlacement
FROM havbed$
group by placement
) X
ON P.Placement = X.Placement
In this instruction the SELECT COUNT(*) will return the totals from your tables, the main SELECT will fill the holes.
I'm supposing you have a 'Placements' table that contains these values from 1 to 10. If not you can substitute that 'Placements' table by (SELECT 1 AS Placement UNION SELECT 2 UNION ...SELECT 10)
Do you think it would be better to create 10 variables to hold the values for each placement and then utilize the stuff function to return the values vertically.
I'm not sure to understand your purpose, but now the solution looks simple enough.
Well, now is time to finish working. If this code helps you I will finish the journey in satisfaction. Regards,
Francesc
June 9, 2011 at 9:28 am
Thank you for all your help. Problem has been resolved.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply