format query result

Question

Post reply

format query result

abhas

SSCarpal Tunnel

Points: 4681
More actions
September 14, 2016 at 1:18 am

#329794

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

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( @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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

ChrisM@Work (9/14/2016)

You could get someone up the duff with all that stuffing 🙂

It's the Right Stuff:-D

😎

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

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

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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