ALIGNMENT
-
Hello all,
I have the quest to make Procedure again.
But this time I have the syntax, but I dont know how to make it into SP with variable @length, @space, @left/right align ?
so when I execute :1. EXEC USP_ALIGN /*maybe example the name of SP*/ , 5, UP, LEFT
--RESULT:
A
A A
A A A
A A A A
A A A A A2. EXEC USP_ALIGN /*maybe example the name of SP*/ , 5, DOWN, RIGHT
--RESULT:
A A A A A
A A A A
A A A
A A
APlease kindly see the attachment image
DECLARE @cnt INTSET @cnt = 1
WHILE @cnt <= 5
BEGIN
PRINT REPLICATE('a ', @cnt)
SET @cnt += 1
END
print ' '
--DECLARE @cnt INT
SET @cnt = 5
WHILE @cnt >= 0
BEGIN
print REPLICATE('A ', @cnt)
SET @cnt -= 1
END
print ' '
--DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= 5
BEGIN
print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
SET @cnt += 1
END
print ' '
--DECLARE @cnt INT
SET @cnt = 5
WHILE @cnt >= 0
BEGIN
print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
SET @cnt -= 1
ENDIf I select all of those syntax it will get the result:
-
Interview question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Wednesday, December 13, 2017 5:04 AM
Not an Interview, but it user request said my boss 🙁
-
My next question would be WHY?
This feels like it is a presentation issue and should be handled in the presentation layer (report or view) and not in the data layer.
unless you are creating ASCII art then you have probably given us an absctraction of the code output you THINK you are looking for. Can you give us the actual business problem and there may be a better solution.
-
Try this...
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.usp_Align
/* =================================================================================================================================
12/13/2017 JL, Created for forum post: https://www.sqlservercentral.com/Forums/1912528/ALIGNMENT
================================================================================================================================= */
/*
dbo.usp_Align
@cnt = 25,
@v_dir = 'D',
@h_dir = 'R';
*/
@cnt INT,
@v_dir CHAR(1),
@h_dir CHAR(1)
AS
BEGIN
SET NOCOUNT ON;DECLARE @_output VARCHAR(8000) = '';
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
cte_Tally (rep_val) AS (
SELECT TOP (@cnt)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b
),
cte_build_string AS (
SELECT
t.rep_val,
pv.pad_val,
ov.output_val
FROM
cte_Tally t
CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
CROSS APPLY ( VALUES (
REPLICATE(CASE WHEN @h_dir = 'L' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'L' THEN t.rep_val ELSE pv.pad_val END)
+ REPLICATE(CASE WHEN @h_dir = 'R' THEN 'A' ELSE ' ' END, CASE WHEN @h_dir = 'R' THEN t.rep_val ELSE pv.pad_val END)
) ) ov (output_val)
)
SELECT
@_output = (
SELECT
CONCAT(@_output, '||', bs.output_val)
FROM
cte_build_string bs
ORDER BY
CASE WHEN @v_dir = 'U' THEN bs.rep_val ELSE bs.pad_val END
FOR XML PATH('')
);PRINT(REPLACE(@_output, '||', CHAR(13) + CHAR(10)));
END;
GOSample output...
dbo.usp_Align
@cnt = 10,
@v_dir = 'U',
@h_dir = 'L';
-----------------------------------
A
AA
AAA
AAAA
AAAAA
AAAAAA
AAAAAAA
AAAAAAAA
AAAAAAAAA
AAAAAAAAAAdbo.usp_Align
@cnt = 10,
@v_dir = 'D',
@h_dir = 'L';
-----------------------------------
AAAAAAAAAA
AAAAAAAAA
AAAAAAAA
AAAAAAA
AAAAAA
AAAAA
AAAA
AAA
AA
Adbo.usp_Align
@cnt = 10,
@v_dir = 'U',
@h_dir = 'R';
---------------------------------- (the raggedness is forum formatting)
A
AA
AAA
AAAA
AAAAA
AAAAAA
AAAAAAA
AAAAAAAA
AAAAAAAAA
AAAAAAAAAAdbo.usp_Align
@cnt = 10,
@v_dir = 'D',
@h_dir = 'R';
---------------------------------- (the raggedness is forum formatting)
AAAAAAAAAA
AAAAAAAAA
AAAAAAAA
AAAAAAA
AAAAAA
AAAAA
AAAA
AAA
AA
A - aaron.reese - Wednesday, December 13, 2017 6:46 AM
Sounds more like a 'show me that you really can code T-SQL' exercise. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - aaron.reese - Wednesday, December 13, 2017 6:46 AMJason A. Long - Wednesday, December 13, 2017 1:57 PMGilaMonster - Wednesday, December 13, 2017 3:08 PM
Hai all,
Im sorry for late Reply,
Im working as IT Consultant as SQL Developer. Perivously as DBA.
And its been 4 months here (placed at Bank) doing New Project, moving sql files, data, and tasks using ssis.
sometimes I get bored if there is an error and wait for the programmer's reply for a long time, so I ask to do something to get rid of my boredom. :))@jason A. long --- thank you for the scriptBtw (By The Way) I think I got my answer, with my own and please advice if there is need more syntax or anything..lets discuss..
Create PROCEDURE USP_ALIGNMENT (@OPT TINYINT, @PIL VARCHAR(5),@JUSTIFY VARCHAR(10))
AS
DECLARE @cnt INtIF @OPT = 1
BEGIN
SET @cnt = 1
WHILE @cnt <= 5
BEGIN
set @pil = 'UP'
SET @Justify = 'LEFT'
PRINT REPLICATE('a ', @cnt)
SET @cnt += 1
END
ENDELSE IF @OPT = 2
BEGIN
--DECLARE @cnt INT
SET @cnt = 5
WHILE @cnt >= 0
BEGIN
set @pil = 'DOWN'
SET @Justify = 'LEFT'
print REPLICATE('A ', @cnt)
SET @cnt -= 1
END
ENDELSE IF @OPT = 3
BEGIN
--DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= 5
BEGIN
set @pil = 'UP'
SET @Justify = 'RIGHT'
print RIGHT(space(20) + REPLICATE('A ', @cnt),20)
SET @cnt += 1
END
ENDELSE IF @OPT = 4
BEGIN
--DECLARE @cnt INT
SET @cnt = 10
WHILE @cnt >= 0
BEGIN
set @pil = 'DOWN'
SET @Justify = 'RIGHT'
print RIGHT(space(20) + REPLICATE('a ', @cnt),20)
SET @cnt -= 1
ENDThank you very much
- Jason A. Long - Wednesday, December 13, 2017 1:57 PM
Hi Jason,
This sp really work..Cool..thank youI do not understand about using 'With' and 'cte'
-
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Wednesday, December 13, 2017 11:36 PM
thank you GilaMonster
- unas_sasing - Wednesday, December 13, 2017 8:10 PM
I'm glad you like the proc code... As Gail already pointed out the The "WITH CTE" is a common table expression.
What's more important is the code that in the CTEs... It's what's commonly referred to as a "Ben-Gan Tally Table". Named for Itzik Ben-Gan, the brilliant guy who came up with the idea.
What makes it special is the fact that it's able to (almost magically) materialize new rows in a set based manner, making it orders of magnitude faster than cursors, while loops & recursive CTEs.That said, looking at the code I posted yesterday, there are two flaws (or at least two things I'd change)...
1) Using a scalar variable to "print" the results, limits the output to 8000 total characters. This would apply even if you were to change the data type to VARCHAR(MAX). Assuming my math is correct, that means my code is limited to a maximum @cnt value of 88 before values start getting truncated.
2) When the values are "Left" aligned, there should be no reason to "pad" the values to the right... Meaning that it's doing more work and taking up more space than necessary.Both should be fairly easy fixes. If I have time, I'll try to fix both later today.
-
This version should be better for @cnt values over 44 (I also missed the space between the replication values the 1st time).
SET NOCOUNT ON;DECLARE
@cnt INT = 150,
@v_dir CHAR(1) = 'D',
@h_dir CHAR(1) = 'R';IF OBJECT_ID('tempdb..#_temp', 'U') IS NOT NULL
BEGIN DROP TABLE #_temp; END;DECLARE
@_print_row INT = 0,
@_output VARCHAR(8000) = '';WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
cte_Tally (rep_val) AS (
SELECT TOP (@cnt)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b
)
SELECT
t.rep_val,
pv.pad_val,
ov.output_val
INTO #_temp
FROM
cte_Tally t
CROSS APPLY ( VALUES (@cnt - t.rep_val) ) pv (pad_val)
CROSS APPLY ( VALUES (CASE WHEN @h_dir = 'R' THEN REPLICATE(' ', pv.pad_val) ELSE '' END + REPLICATE('A ', t.rep_val)
) ) ov (output_val);WHILE EXISTS (SELECT 1 FROM #_temp t WHERE (@v_dir = 'U' AND t.rep_val > @_print_row) OR (@v_dir = 'D' AND t.pad_val > @_print_row) )
BEGIN
SET @_print_row = @_print_row + 1;SELECT
@_output = t.output_val
FROM
#_temp t
WHERE
(t.rep_val = @_print_row AND @v_dir = 'U')
OR
(t.pad_val = @_print_row AND @v_dir = 'D');PRINT(@_output);
END; - Jason A. Long - Thursday, December 14, 2017 7:43 AMJason A. Long - Thursday, December 14, 2017 11:43 AM
Thank you for the explanation and the scripts.
It help me a lot. I will keep learning..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply