March 26, 2012 at 9:00 am
Hi All,
i have this data:
DECLARE @tblCron TABLE(
id INT ,Nivel INT,
A INT,
B INT,
C INT,
D INT
)
INSERT @tblCron
SELECT 1202337,1,0,0,0,0UNION ALL
SELECT 1202338,2,0,0,0,0UNION ALL
SELECT 1202339,3,0,0,0,0UNION ALL
SELECT 1202340,4,7,7,7,4UNION ALL
SELECT 1202341,4,4,7,8,2UNION ALL
SELECT 1202342,1,0,0,0,0UNION ALL
SELECT 1202343,2,0,0,0,0UNION ALL
SELECT 1202344,3,0,0,0,0UNION ALL
SELECT 1202345,4,7,7,7,4
SELECT * FROM @tblCron
I need the result to be (with a select statement):
see attachment
____________________________________________________________________________
Rafo*
March 26, 2012 at 9:24 am
What have you tried so far?
_________________________________
seth delconte
http://sqlkeys.com
March 26, 2012 at 9:29 am
I am curious why you are formating the output in the SQL code. What you are asking for should really be done in the UI.
March 26, 2012 at 9:35 am
Really? Did you actually try this on your own first?
SELECT id, Nivel,
CASE WHEN A = 0 THEN '' ELSE CAST(A AS CHAR(1)) END AS A,
CASE WHEN B = 0 THEN '' ELSE CAST(B AS CHAR(1)) END AS B,
CASE WHEN C = 0 THEN '' ELSE CAST(C AS CHAR(1)) END AS C,
CASE WHEN D = 0 THEN '' ELSE CAST(D AS CHAR(1)) END AS D
FROM @tblCron
Lynn Pettis (3/26/2012)
I am curious why you are formating the output in the SQL code. What you are asking for should really be done in the UI.
Yeah, that too ^^
March 26, 2012 at 9:47 am
REPLACE works too 😀
SELECT id,Nivel,REPLACE(A,'0','')A,REPLACE(B,'0','')B...
FROM @tblCron
_________________________________
seth delconte
http://sqlkeys.com
March 26, 2012 at 10:12 am
seth delconte (3/26/2012)
REPLACE works too 😀
SELECT id,Nivel,REPLACE(A,'0','')A,REPLACE(B,'0','')B...FROM @tblCron
I try to avoid implicit conversions where possible
March 26, 2012 at 11:42 am
Cadavre (3/26/2012)
seth delconte (3/26/2012)
REPLACE works too 😀
SELECT id,Nivel,REPLACE(A,'0','')A,REPLACE(B,'0','')B...FROM @tblCron
I try to avoid implicit conversions where possible
The other thing is that it's not a general solution. Consider what would happen if the data include 10 instead of just single digit numbers. I seriously doubt that the OP wants the output to be "1" in that case.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 26, 2012 at 1:15 pm
That data r results from a dynamic query,
well that result i input to a temporary table, and then i made a update.
my test query:
DECLARE @SQL NVARCHAR(MAX)
DECLARE @colCampoREPLACE VARCHAR(MAX)
DECLARE @C INT=1
DECLARE @cc INT=10
SET @colCampoREPLACE=''
BEGIN
--'C'+CONVERT(VARCHAR,@C)
SET @colCampoREPLACE = @colCampoREPLACE + 'c'+ CONVERT(VARCHAR(2),@C) +'='''','
SET @C=@C+1
END
SET @colCampoREPLACE=SUBSTRING(@colCampoREPLACE,1,LEN(@colCampoREPLACE)-1)
print @colCampoREPLACE
SET @SQL='
UPDATE #tbl_Cron
SET '+@colCampoREPLACE+'
WHERE NIVEL_partida_cliente in (1,2,3)
'
EXEC sp_executesql @SQL
print @SQL
The problem was solved, but maybe exists anothe best way :ermm:
____________________________________________________________________________
Rafo*
March 26, 2012 at 1:18 pm
xRafo (3/26/2012)
That data r results from a dynamic query,well that result i input to a temporary table, and then i made a update.
my test query:
DECLARE @SQL NVARCHAR(MAX)DECLARE @colCampoREPLACE VARCHAR(MAX)
DECLARE @C INT=1
DECLARE @cc INT=10
SET @colCampoREPLACE=''
BEGIN
--'C'+CONVERT(VARCHAR,@C)
SET @colCampoREPLACE = @colCampoREPLACE + 'c'+ CONVERT(VARCHAR(2),@C) +'='''','
SET @C=@C+1
END
SET @colCampoREPLACE=SUBSTRING(@colCampoREPLACE,1,LEN(@colCampoREPLACE)-1)
print @colCampoREPLACE
SET @SQL='
UPDATE #tbl_Cron
SET '+@colCampoREPLACE+'
WHERE NIVEL_partida_cliente in (1,2,3)
'
EXEC sp_executesql @SQL
print @SQL
The problem was solved, but maybe exists anothe best way :ermm:
First, I really don't see how this solves your problem. And two, you haven't answered the question as to why you want to return blanks for 0's in your query instead of allowing the UI to handle the proper display of data.
March 26, 2012 at 1:26 pm
Dependin of the column "Nivel",
if that have a value of 1 or 2 or 3 the columns 'A','B','C','D' must be empty,
____________________________________________________________________________
Rafo*
March 26, 2012 at 1:30 pm
I will ask again, why can't this be handled by the UI? Why do you have to do it in the select statement?
March 26, 2012 at 1:33 pm
For the perfomance mostly..
____________________________________________________________________________
Rafo*
March 26, 2012 at 1:36 pm
I think the UI would be the better place for this then, I don't see doing the conversion in the query being better for performance.
March 26, 2012 at 2:02 pm
xRafo (3/26/2012)
For the perfomance mostly..
Meaning you want to slow down sql to format data for display? 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2012 at 2:11 pm
The select of this UI is a PIVOT statement.
with the query att. the perfomance is OK, and if i handle by the UI i have the read 1 record by 1 and asking her "nivel" after change the 0 for the entire row empty.
Sorry, bad engl.
____________________________________________________________________________
Rafo*
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply