Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Wednesday, May 28, 2014
Friday, May 24, 2013
format number with comas in select
1. select statement to format number with decimals and without, using money type and convert function
;WITH cte(d) AS (SELECT CONVERT(VARCHAR, CONVERT(MONEY, 123456789), 1) AS delimited_number) SELECT d , REPLACE(d,'.00', '') sol1 , SUBSTRING(d, 1, LEN(d)-3) sol2 , PARSENAME(d, 2) sol3 FROM cte2. result of select
d sol1 sol2 sol3 ----------------- ------------------ ------------------ ------------------ 123,456,789.00 123,456,789 123,456,789 123,456,789
transfer multirow parameters to one var
1. Create test table
CREATE TABLE dbo.Params( id int IDENTITY(1,1) NOT NULL , param varchar(50) NULL )2. Add test records for parameters
INSERT INTO dbo.Params (param) VALUES ('some = 1') , ('some = 2') , ('some = 3') , ('some = 4') , ('some = 5')3. Get parameters as one row using PIVOT
SELECT isnull([1],'') [param1] , isnull([2],'') [param2] , isnull([3],'') [param3] , isnull([4],'') [param4] , isnull([5],'') [param5] FROM ( SELECT row_number() OVER (ORDER BY param) AS rn, param FROM dbo.Params WHERE param IS NOT NULL ) S PIVOT ( MAX(param) FOR rn IN ([1],[2],[3], [4], [5]) ) AS PivotTable;4. Result of SELECT
param1 param2 param3 param4 param5 --------- --------- --------- --------- ---------- some = 1 some = 2 some = 3 some = 4 some = 5 (1 row(s) affected)5. Use variable expression to get one string
(@[User::param1] == "")?"":@[User::param1] + ( (@[User::param2] == "")?"": " AND " + @[User::param2] ) + ( (@[User::param3] == "")?"": " AND " + @[User::param3] ) + ( (@[User::param4] == "")?"": " AND " + @[User::param4] ) + ( (@[User::param5] == "")?"": " AND " + @[User::param5] )6. Now we have parameters combined in one variable
some = 1 AND some = 2 AND some = 3 AND some = 4 AND some = 5
Subscribe to:
Posts (Atom)