Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

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 cte

2. 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