1) Query to combine two columns but second columns called Name and Price.
Name max length will be 60. Then after 60 length price column will start.
SELECT Name
,Price
,CONCAT (
LEFT(CONCAT (
Name
,Replicate(' ', 60)
), 45)
,Price
) AS ConcatedString
FROM [portfolio];
Here name coulmn values current lenght is different however when we combine using price it should show padding.
This can be achieved using the LEFT and Replicate function.
2)If there is multiple fields we need to follow the same
DECLARE @Table AS TABLE (Val VARCHAR(4000))
INSERT INTO @Table
SELECT CONCAT (
'Name'
,Replicate(' ', 36)
,'Price'
,Replicate(' ', 10)
,'Price Change'
)
INSERT INTO @Table
SELECT CONCAT (
LEFT(CONCAT (
Name
,Replicate(' ', 60)
), 40)
,LEFT(CONCAT (
Price
,Replicate(' ', 15)
), 15)
,[Price Change]
)
FROM [portfolio]
SELECT *
FROM @Table
The above query information can be achieved by using LPAD and RPAD functions.
unfortunately SQL server do not have LPAD and RPAD functionality.
It be achieved by creating function by using LPAD and RPAD functions.
2) RPAD and LPAD Function in SQL Server
SQL Server by default do not have RPAD and LPAD functions.
CREATE FUNCTION dbo.LPAD (
@Column VARCHAR(100)
,@PadString VARCHAR(10)
,@length INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Resultvar VARCHAR(100);
SELECT @Resultvar = LEFT(CONCAT (
REPLICATE(@PadString, @length - LEN(@Column))
,@Column
), @length);
RETURN @Resultvar
END
CREATE FUNCTION dbo.RPAD (
@Column VARCHAR(100)
,@PadString VARCHAR(10)
,@length INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Resultvar VARCHAR(100);
SELECT @Resultvar = RIGHT(CONCAT (
@Column
,REPLICATE(@PadString, @length - LEN(@Column))
), @length);
RETURN @Resultvar
END
The query can be re written like the below
SELECT CONCAT (
dbo.LPAD(Name, ' ', 60)
,dbo.LPAD(Price, ' ', 60)
,[Price Change]
)
FROM [portfolio]
2) Query to Create a table from 1 to 9 contain each number filled with one cell and total sum should be same.
That means the sum should be equal for each row (horizontally when we sum row) and
each column (when we sum all column values)
to achieve this we need to create a function that will return a table
for a row that sums to 15 by combining col1 ,col2 and col3 in row1
Then list of the 3*3 table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.SudokuTable (
@minval INT
,@maxval INT
)
RETURNS @SudokuTable TABLE (
FirstColumn INT
,SecondColumn INT
,ThirdColumn INT
)
AS
BEGIN
DECLARE @rc1 INT
,@rc2 INT
,@rc3 INT
,@row1 INT
,@inc INT
,@sumval INT
SET @sumval = 15
SET @inc = 1
SET @rc1 = 1
WHILE @rc1 <= @maxval
BEGIN
SET @rc2 = @inc
SET @rc3 = @inc
WHILE (@rc2 <= @maxval)
BEGIN
SET @rc3 = @inc
IF @rc1 = @rc2
SET @rc2 = @rc2 + @inc
IF @rc1 = @rc3
SET @rc3 = @rc3 + @inc
WHILE (@rc3 <= @maxval)
BEGIN
SET @row1 = sum(@rc1 + @rc2 + @rc3)
IF (@row1 = @sumval)
BEGIN
INSERT INTO @SudokuTable
VALUES (
@rc1
,@rc2
,@rc3
)
END
SET @rc3 = @rc3 + @inc
IF (@rc3 = @rc2)
SET @rc3 = @rc3 + @inc
IF (@rc1 = @rc3)
SET @rc3 = @rc3 + @inc
END
SET @rc2 = @rc2 + @inc
SET @rc3 = @rc1 + @inc
END
SET @rc1 = @rc1 + @inc
END
RETURN
END
GO
The above will generates table have all possibilites to create
15 for each row.
Now we got all list, from that we need to make 3 rows .
SET NOCOUNT ON
DECLARE @SudokuTable AS TABLE (
ID INT identity
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
CREATE TABLE #temp1 (
ID INT identity(1, 1)
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
CREATE TABLE #temp2 (
ID INT identity(1, 1)
,FirstColumn INT
,SecondColumn INT
,Thirdcolumn INT
)
DECLARE @Finaltable AS TABLE (List VARCHAR(100))
DECLARE @minvalue INT
,@inc INT = 1
,@maxvalue INT
,@r1c1 INT
,@r1c2 INT
,@r1c3 INT
,@r2c1 INT
,@r2c2 INT
,@r2c3 INT
,@r3c1 INT
,@r3c2 INT
,@r3c3 INT
DECLARE @secondminval INT
,@secondmaxval INT
,@thirdminval INT
,@thirdmaxval INT
,@setval INT = 1
INSERT INTO @SudokuTable
SELECT *
FROM [dbo].[SudokuTable](1, 9)
SELECT @maxvalue = max(ID)
,@minvalue = min(ID)
FROM @SudokuTable
WHILE (@minvalue <= @maxvalue)
BEGIN
SELECT @r1c1 = FirstColumn
,@r1c2 = SecondColumn
,@r1c3 = Thirdcolumn
FROM @SudokuTable
WHERE ID = @minvalue
INSERT INTO #temp1
SELECT FirstColumn
,SecondColumn
,Thirdcolumn
FROM @SudokuTable
WHERE FirstColumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)
AND SecondColumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)
AND Thirdcolumn NOT IN (
@r1c1
,@r1c2
,@r1c3
)
SELECT @secondmaxval = max(ID)
,@secondminval = min(ID)
FROM #temp1
WHILE (@secondminval <= @secondmaxval)
BEGIN
SELECT @r2c1 = FirstColumn
,@r2c2 = SecondColumn
,@r2c3 = Thirdcolumn
FROM #temp1
WHERE ID = @secondminval
INSERT INTO #temp2
SELECT FirstColumn
,SecondColumn
,Thirdcolumn
FROM #temp1
WHERE FirstColumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)
AND SecondColumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)
AND Thirdcolumn NOT IN (
@r2c1
,@r2c2
,@r2c3
)
SELECT @thirdmaxval = max(ID)
,@thirdminval = min(ID)
FROM #temp2
WHILE (@thirdminval <= @thirdmaxval)
BEGIN
SELECT @r3c1 = FirstColumn
,@r3c2 = SecondColumn
,@r3c3 = Thirdcolumn
FROM #temp2
WHERE ID = @thirdminval
IF (
sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c2 + @r2c2 + @r3c2)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c3 + @r3c3)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c1 + @r2c2 + @r3c3)
AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c2 + @r3c1)
)
BEGIN
INSERT INTO @Finaltable
VALUES (
CONCAT (
'##Set '
,@setval
,' for Sudoko##'
)
)
INSERT INTO @Finaltable
VALUES (
CONCAT (
@r1c1
,' '
,@r1c2
,' '
,@r1c3
)
)
,(
CONCAT (
@r2c1
,' '
,@r2c2
,' '
,@r2c3
)
)
,(
CONCAT (
@r3c1
,' '
,@r3c2
,' '
,@r3c3
)
)
SET @setval = @setval + @inc
END
SET @thirdminval = @thirdminval + @inc
END
TRUNCATE TABLE #temp2
SET @secondminval = @secondminval + @inc
END
TRUNCATE TABLE #temp1
SET @minvalue = @minvalue + @inc
END
SELECT *
FROM @Finaltable
No comments:
Post a Comment