--/* 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)
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
SET
@c = @c + 1
END
SET
@Select_Code = 'SELECT '+@Asterik_Code
+' FROM Astrick'
EXECUTE(@Select_Code)