When I was thinking about the programming logic
,I suddenly remember my old graduation days where i generally used to run the
program in C/CPP the programs like printing of * in piramid or in reverse of it.
as now I am SQL Developer I tried to implement the same in SQL SERVER 2008.
I tried the above code, if you find other approch or find some any good solution
please replly here.
Please run the following or attached code in text mode of SQL SERVER MANAGEMENT
STUDIO 2008.
--/* code
is generated on the basis of geralization method
-- you can check by taking as much as column you
want and check like
-- say suppose i am taking 9 columns where i am
inserting * it is printing Pyramid like
-- but while running following code select Result
to text
-- * * * * * * * * *
-- * * * * * * *
-- * * * * *
-- * * *
-- * *
-- *
--*/
IF(EXISTS(SELECT
OBJECT_ID('Astrick')))
DROP TABLE
Astrick
CREATE TABLE
Astrick
(
id
INT IDENTITY(1,1)
,C1 VARCHAR(10)
,C2 VARCHAR(10)
,C3 VARCHAR(10)
,C4 VARCHAR(10)
,C5 VARCHAR(10)
,C6 VARCHAR(10)
,C7 VARCHAR(10)
,C8 VARCHAR(10)
,C9 VARCHAR(10)
)
DECLARE @i INT,@i1
INT,@HOLD
INT,@i2
INT,@HOLD2
INT,@loop
INT
DECLARE @INSERT VARCHAR(MAX)
-- column name of insert query
DECLARE @Insert_Query
VARCHAR(MAX)
--- Insert query
DECLARE @VALUE VARCHAR(MAX)
--- value part of insert query
SET @I1=1
SELECT @I2=COUNT(*)
FROM sys.columns
S WITH(NOLOCK)
JOIN sys.all_objects
O WITH(NOLOCK)
ON S.object_id=O.object_id
WHERE O.name
LIKE 'Astrick'
and collation_name is
not null
SET @loop = @i2
SET @i = @i2
SET @INSERT=''
SET @VALUE=''
WHILE(@i
>0)
BEGIN
SET @HOLD = @I1
SET @HOLD2 = @I2
WHILE(@I1
<=@I2)
BEGIN
IF(@I1=@I2)
BEGIN
SET @INSERT =
@INSERT + 'C'+CONVERT(VARCHAR(10),@I1)
SET @VALUE=@VALUE
+''' *'''
END
ELSE
BEGIN
SET @INSERT=@INSERT
+ 'C'+CONVERT(VARCHAR(10),@I1)+','
SET @VALUE =@VALUE
+ ''' *'','
END
SET @I1 = @I1
+1
END
SET @Insert_Query =
'INSERT INTO Astrick('+@INSERT+')
VALUES('+@VALUE+')'
EXECUTE(@Insert_Query)
SET @i = @i
-2
SET @I1 = @HOLD
+ 1
SET @I2 = @HOLD2
-1
SET @INSERT=''
-- resetting string to blank to use it agin in the
same loop
SET @VALUE=''
END
--- code to print Asteric table
SET NOCOUNT
ON
DECLARE @Asterik_Code
VARCHAR(MAX),@Select_Code
VARCHAR(MAX)
DECLARE @c INT
SET @c=1
SET @Asterik_Code =''
WHILE(@c<=@loop)
BEGIN
IF(@c=@loop)
BEGIN
SET
@Asterik_Code=@Asterik_Code+
'isnull('+
( SELECT
a.name FROM
(
SELECT s.name,ROW_NUMBER()
OVER(ORDER
BY s.name)
id FROM sys.columns
S WITH(NOLOCK)
JOIN
sys.all_objects
O WITH(NOLOCK)
ON S.object_id=O.object_id
WHERE
O.name LIKE
'Astrick' and
collation_name is not
null) a WHERE a.id=@c)
+','''')'
END
ELSE
BEGIN
SET @Asterik_Code=@Asterik_Code+
'isnull('+
( SELECT
a.name FROM
(
SELECT s.name,ROW_NUMBER()
OVER(ORDER
BY s.name)
id FROM sys.columns
S WITH(NOLOCK)
JOINys.all_objects
O WITH(NOLOCK)
ON S.object_id=O.object_id
WHERE
O.name LIKE
'Astrick' and
collation_name is not
null) a WHERE a.id=@c)
+',''''),'
END
SET @c = @c
+ 1
END
SET @Select_Code =
'SELECT '+@Asterik_Code
+' FROM Astrick'
EXECUTE(@Select_Code)