Column Alias name dynamically

Question

Column Alias name dynamically

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
February 22, 2012 at 9:14 am

#257254

Dear All
In the user tables each table create the alias name dynamically
to increment the alphabetic only..
select o.name from sys.sysobjects o
where xtype='u'
tablename aliasname
studentmaster AAAAA
studentdetails AAABA
coursedetails AAAAC
....
table_name ZZZZZ
any one help me to create the alias name dynamically
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

your question is not clear, and your pattern does not make sense.

Are you trying to have a value in the column "alias name" increment with letters? On insert or are you calling a function which would create the next value for you?

drew.allen SSC Guru Points: 76987 More actions · Answer 2

WHY? How are you planning on using these aliases? Any queries written using these aliases would be virtually impossible to read. You're better off using aliases that are related to the table name in some way. For example,

StudentMaster AS sm

StudentDetails AS sd

CourseDetails AS cd

Table_Name AS tn

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

CELKO (2/23/2012)
WHY? The last time I saw anything like this, the goal was to get physical labels for mag tapes. What are you trying to do?

He need to label rolls of punched tape. Do you remember one?

http://en.wikipedia.org/wiki/Punched_tape

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Wow. . . OK.

This will generate your 11,881,376 (!!) combinations

SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha

FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) a

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) b

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) c

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) d

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) e

After that, I guess decide on the ordering of your tables with a ROW_NUMBER() then decide on the ordering of the "alias" and assign a ROW_NUMBER() to each of them then finally an INNER JOIN.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 5

Cadavre: Here is another way to generate the table:

WITHAlphabet

AS(SELECT CHAR(N) AS Letter FROM Tally WHERE N BETWEEN 65 AND 90),

TwoLetter

AS(SELECT a.Letter + b.Letter AS Letters FROM Alphabet a CROSS JOIN Alphabet b),

ThreeLetter

AS(SELECT a.Letters + b.Letter AS Letters FROM TwoLetter a CROSS JOIN Alphabet b),

FourLetter

AS(SELECT a.Letters + b.Letter AS Letters FROM ThreeLetter a CROSS JOIN Alphabet b),

FiveLetter

AS(SELECT a.Letters + b.Letter AS Letters FROM FourLetter a CROSS JOIN Alphabet b)

SELECT*

FROMFiveLetter

ORDER BY Letters

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

fahey.jonathan (2/24/2012)
Cadavre: Here is another way to generate the table:

OK, I'll play. . .

Your code:

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS (SELECT 1 FROM t1 x, t1 y),

t3(N) AS (SELECT 1 FROM t2 x, t2 y),

Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),

Alphabet AS(SELECT CHAR(N) AS Letter FROM Tally WHERE N BETWEEN 65 AND 90),

TwoLetter AS (SELECT a.Letter + b.Letter AS Letters FROM Alphabet a CROSS JOIN Alphabet b),

ThreeLetter AS (SELECT a.Letters + b.Letter AS Letters FROM TwoLetter a CROSS JOIN Alphabet b),

FourLetter AS (SELECT a.Letters + b.Letter AS Letters FROM ThreeLetter a CROSS JOIN Alphabet b),

FiveLetter AS (SELECT a.Letters + b.Letter AS Letters FROM FourLetter a CROSS JOIN Alphabet b)

SELECT *

FROM FiveLetter

ORDER BY Letters

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

(11881376 row(s) affected)

Table 'Worktable'. Scan count 4, logical reads 1415000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 56269 ms, elapsed time = 115062 ms.

My code:

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha

FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) a

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) b

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) c

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) d

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) e

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

(11881376 row(s) affected)

SQL Server Execution Times:

CPU time = 8924 ms, elapsed time = 69863 ms.

So your code is around 40 seconds slower if we create the tally table on the fly.

If we execute your code against a physical tally table then I get System.OutOfMemoryException on my little test box (can't run on my bigger test box, it's busy), but even that takes 20 seconds longer to just to throw the error than mine does to execute.

There's a small chance that this:

SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',b.N,1)

+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',c.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',d.N,1)+

SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',e.N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)

CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) b(N)

CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) c(N)

CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) d(N)

CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) e(N)

May be slightly quicker, but I suspect it'll perform about the same.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 7

I agree that your version is faster, but not by the margin indicated. Most of the difference is created by the ORDER BY clause in "my" code, which is absent in yours.

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

fahey.jonathan (2/24/2012)
I agree that your version is faster, but not by the margin indicated. Most of the difference is created by the ORDER BY clause in "my" code, which is absent in yours.

Okies. .

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha

FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) a

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) b

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) c

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) d

CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

(9),(10),(11),(12),(13),(14),(15),

(16),(17),(18),(19),(20),(21),(22),

(23),(24),(25),(26)) a(N)) e

ORDER BY a.alpha+b.alpha+c.alpha+d.alpha+e.alpha

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

(11881376 row(s) affected)

SQL Server Execution Times:

CPU time = 63819 ms, elapsed time = 86214 ms.

It's all slow, but still 20 seconds quicker --edit-- on my small test box. I guess the creation time is fairly unimportant, since I suppose you'd create a look-up table with the values in so would only need to execute it once.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Sorry... double post removed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

Cadavre (2/24/2012)
Wow. . . OK.
This will generate your 11,881,376 (!!) combinations
SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)) a
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)) b
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)) c
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)) d
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) a(N)) e
After that, I guess decide on the ordering of your tables with a ROW_NUMBER() then decide on the ordering of the "alias" and assign a ROW_NUMBER() to each of them then finally an INNER JOIN.

Now, count the number of combinations that contain swear words. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)