September 14, 2016 at 1:18 am
i have to write q query which would display result as below:
Department Name Salary Commission
========== ========== ==========
30 Mike 1600 300
30 GIRISH 1250 500
30 MARUTI 1250 1400
30 MANOJ 1500 0
but here constraints are that, output of Department should start at position 1 and end at 10. if size is small then put "0" before it. in above case it should be : "0000000030"
Name should start at 11 to 30. If name having less characters put blank space after that.
Salary should start at position on 31 in the file.
Can i achieve this in SQL query only? please help
Thanks,
Abhas.
September 14, 2016 at 1:51 am
Quick suggestion
😎
BTW DON'T USE the FORMAT function!
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @LINE_TEMPLATE VARCHAR(50) = '000000000 ';
;WITH SAMPLE_DATA(Department,Name,Salary,Commission) AS
( SELECT Department,Name,Salary,Commission FROM
(VALUES
(30,'Mike' ,1600, 300)
,(30,'GIRISH',1250, 500)
,(30,'MARUTI',1250,1400)
,(30,'MANOJ' ,1500, 0)
) AS X(Department,Name,Salary,Commission)
)
SELECT
STUFF(
STUFF(
STUFF(
STUFF( @LINE_TEMPLATE,11,LEN(SD.Name),SD.Name)
,31,CONVERT(INT,FLOOR(LOG10( SD.Salary )) + 1,0),CONVERT(VARCHAR(10),SD.Salary))
,41,CONVERT(INT,FLOOR(LOG10( ISNULL(NULLIF(SD.Commission,0),1) )) + 1,0),CONVERT(VARCHAR(10),SD.Commission))
,10 - CONVERT(INT,FLOOR(LOG10( SD.Department )),0),CONVERT(INT,FLOOR(LOG10( SD.Department )) + 1,0),CONVERT(VARCHAR(10),SD.Department))
FROM SAMPLE_DATA SD;
Output
---------------------------------------------
0000000030Mike 1600 300
0000000030GIRISH 1250 500
0000000030MARUTI 1250 1400
0000000030MANOJ 1500 0
September 14, 2016 at 1:56 am
WITH SampleData AS (SELECT * FROM (VALUES
('30', CAST('Mike' AS VARCHAR(30)), 1600, 300),
('30', 'GIRISH', 1250, 500),
('30', 'MARUTI', 1250, 1400),
('30', 'MANOJ', 1500, 0)
) d (Department, [Name], Salary, Commission))
SELECT
OutputString = RIGHT('000000000'+Department,10)
+ LEFT([Name]+SPACE(20),20)
+ LEFT(CAST(Salary AS VARCHAR(10))+SPACE(10),10)
+ LEFT(CAST(Commission AS VARCHAR(10))+SPACE(10),10)
FROM SampleData
OutputString
0000000030Mike 1600 300
0000000030GIRISH 1250 500
0000000030MARUTI 1250 1400
0000000030MANOJ 1500 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2016 at 1:58 am
Eirikur Eiriksson (9/14/2016)
Quick suggestion😎
BTW DON'T USE the FORMAT function!
USE TEEST;GO
SET NOCOUNT ON;
DECLARE @LINE_TEMPLATE VARCHAR(50) = '000000000 ';
;WITH SAMPLE_DATA(Department,Name,Salary,Commission) AS
( SELECT Department,Name,Salary,Commission FROM
(VALUES
(30,'Mike' ,1600, 300)
,(30,'GIRISH',1250, 500)
,(30,'MARUTI',1250,1400)
,(30,'MANOJ' ,1500, 0)
) AS X(Department,Name,Salary,Commission)
)
SELECT
STUFF(
STUFF(
STUFF(
STUFF( @LINE_TEMPLATE,11,LEN(SD.Name),SD.Name)
,31,CONVERT(INT,FLOOR(LOG10( SD.Salary )) + 1,0),CONVERT(VARCHAR(10),SD.Salary))
,41,CONVERT(INT,FLOOR(LOG10( ISNULL(NULLIF(SD.Commission,0),1) )) + 1,0),CONVERT(VARCHAR(10),SD.Commission))
,10 - CONVERT(INT,FLOOR(LOG10( SD.Department )),0),CONVERT(INT,FLOOR(LOG10( SD.Department )) + 1,0),CONVERT(VARCHAR(10),SD.Department))
FROM SAMPLE_DATA SD;
Output
---------------------------------------------0000000030Mike 1600 300
0000000030GIRISH 1250 500
0000000030MARUTI 1250 1400
0000000030MANOJ 1500 0
You could get someone up the duff with all that stuffing 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2016 at 2:03 am
ChrisM@Work (9/14/2016)
You could get someone up the duff with all that stuffing 🙂
It's the Right Stuff:-D
😎
September 14, 2016 at 6:44 am
It's the STUFF that dreams are made of... 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2016 at 5:57 pm
abhas (9/14/2016)
i have to write q query which would display result as below:Department Name Salary Commission
========== ========== ==========
30 Mike 1600 300
30 GIRISH 1250 500
30 MARUTI 1250 1400
30 MANOJ 1500 0
but here constraints are that, output of Department should start at position 1 and end at 10. if size is small then put "0" before it. in above case it should be : "0000000030"
Name should start at 11 to 30. If name having less characters put blank space after that.
Salary should start at position on 31 in the file.
Can i achieve this in SQL query only? please help
Thanks,
Abhas.
Not clear how you want to present Salary and Commission, so pick the option you need from these ones:
SELECT REPLACE(STR(30, 10, 0), ' ', '0') + CONVERT(CHAR(20), 'Mike') + CONVERT(CHAR(10), 1600) + CONVERT(CHAR(10), 300)
SELECT REPLACE(STR(30, 10, 0), ' ', '0') + CONVERT(CHAR(20), 'Mike') + STR(1600, 10, 0) + STR(300, 10, 0)
SELECT REPLACE(STR(30, 10, 0), ' ', '0') + CONVERT(CHAR(20), 'Mike') + REPLACE(STR(1600, 10, 0), ' ', '0') + REPLACE(STR(300, 10, 0), ' ', '0')
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply