is this possible

ranjitjain · Aug 18, 2005

Hi Experts,
I have a temp table with 4 columns and dynamically generated column names.
I want to fire select query with column names to change it to alias names.
something like one said to me in ORACLE
select 1,2,3 from tabe1
i.e. column 1, column 2 etc.
So i want the way we specify nos. in order by clause like order by 1.
Is this possible???
When i fired select 1,2,3 from table1.....its printing no of rows with filled values i.e. 1,2,3.....

FrankKalis · Aug 18, 2005

I don't think this is possible.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

ranjitjain · Aug 18, 2005

Hi Frank,
Is there any other way to get those column names as its my global temp table.

Madhivanan · Aug 18, 2005

I am not sure what you are asking

Is this you wanted?

select [1],[2],[3] from tabe1

Madhivanan

Failing to plan is Planning to fail

ranjitjain · Aug 18, 2005

Hi Madhivanan,
I want to call the columns in select statement by column numbers, the way we call in order by clause i.e. order by 1......

FrankKalis · Aug 18, 2005

You would need a column named "1" in the table for this to work. Actually I think this is a way to get these informations: <pre id="code"> DECLARE @a VARCHAR(50), @b VARCHAR(50), @c VARCHAR(50), @d VARCHAR(50) SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) , @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END) , @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END) FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id WHERE so.type = 'u' AND so.name = 'authors' GROUP BY so.name SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn DECLARE @stmt NVARCHAR(4000) SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM authors' EXEC sp_ExecuteSQL @stmt </pre id="code"> But frankly, I think it's a kludge at best. [<img src='/community/emoticons/emotion-1.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

FrankKalis · Aug 18, 2005

Ooh, try this within the PUBS sample database.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 18, 2005

Try this also

Declare @sql varchar(2000)
Declare @col int
set @col=2
set @sql=''
select @sql=@sql+','+column_name from information_schema.columns
where table_name='yourTable' and Ordinal_position=@col
select @sql='Select '+substring(@sql,2,len(@sql))+' from yourTable'
select @sql
Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 18, 2005

Slight correction

set @col=4
...
where table_name='yourTable' and Ordinal_position<=@col

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 18, 2005

Thanks Frank. Thats Correct
Is my approach correct?

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 18, 2005

I hadn't thought about using aggregate concatenation for this. Looks like there's more than one way to skin that cat. [<img src='/community/emoticons/emotion-1.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Madhivanan · Aug 18, 2005

But I think some times it looks easier [<img src='/community/emoticons/emotion-1.gif' alt='' />] Madhivanan Failing to plan is Planning to fail

ranjitjain · Aug 18, 2005

Thanx guys,
But will this work on temp tables too.
Ive tested on fixed tables and i got what i wanted but trying on temporary table

FrankKalis · Aug 18, 2005

Sure, just replace

where table_name=OBJECT_NAME(OBJECT_ID('##yourtable')) and Ordinal_position<=@col

in Madhivanan's solution or

AND so.name = OBJECT_NAME(OBJECT_ID('##yourtable'))

and it should work.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 18, 2005

Frank, as temp tables are stored like

#tt_________________________________________________________________________________________________________________000000000019

I think the above wont work

So we need to query on tempdb sysobjects

Use tempDb
Select column_name from information_Schema.columns where
table_name=OBJECT_NAME((Select top 1 id from sysobjects where name like '%#temptable%'))

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 19, 2005

Have you tried it? [<img src='/community/emoticons/emotion-5.gif' alt='' />] The fact that a temp table #t under the covers has such a cryptic name as you've mentioned is exactly the reason why I used OBJECT_NAME(OBJECT_ID)) <pre id="code"> DROP TABLE #t CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5)) INSERT INTO #t SELECT 'a', 'b', 'c', 'd' DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5), @d VARCHAR(5) SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) , @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END) , @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END) FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id WHERE so.type = 'u' AND so.name = OBJECT_NAME(OBJECT_ID('#t')) GROUP BY so.name SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn DECLARE @stmt NVARCHAR(4000) SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM #t' EXEC sp_ExecuteSQL @stmt FirstColumn SecondColumn ThirdColumn ForthColumn ----------- ------------ ----------- ----------- c1 c2 c3 c4 (1 row(s) affected) c1 c2 c3 c4 ----- ----- ----- ----- a b c d (1 row(s) affected) </pre id="code"> Btw, a SELECT * might also work. [<img src='/community/emoticons/emotion-2.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Madhivanan · Aug 19, 2005

Well

When I use this I dont get any names

Select column_name from information_Schema.columns where
table_name=OBJECT_NAME(object_Id('#t'))

Madhivanan

Failing to plan is Planning to fail

Adriaan · Aug 19, 2005

Try OBJECT_NAME(object_Id(DB_NAME() + '..#t'))?

FrankKalis · Aug 19, 2005

It is working for me.

DROP TABLE #t
CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c', 'd'
Declare @sql varchar(2000)
Declare @col int
set @col=4
set @sql=''
select @sql=@sql+','+column_name from information_schema.columns
where table_name=OBJECT_NAME(OBJECT_ID('#t')) and Ordinal_position<=@col
select @sql='Select '+substring(@sql,2,len(@sql))+' from #t'
select @sql
Exec(@sql)

----------------------------
Select c1,c2,c3,c4 from #t

(1 row(s) affected)

c1 c2 c3 c4
----- ----- ----- -----
a b c d

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

FrankKalis · Aug 19, 2005

Umh, might be really the reason. I'm always in tempdb when playing with code.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

FrankKalis · Aug 19, 2005

Okay, here's the revamped version of my statement

USE PUBS
SET NOCOUNT ON
DROP TABLE #t
CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c', 'd'
DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5), @d VARCHAR(5)
SELECT
@a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END)
, @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END)
, @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
, @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)
FROM
tempdb..syscolumns sc
INNER JOIN
tempdb..sysobjects so
ON
so.id = sc.id
WHERE
so.type = 'u'
AND
so.id = OBJECT_ID('tempdb..#t')
GROUP BY so.name
SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn
SET NOCOUNT OFF
DECLARE @stmt NVARCHAR(4000)
SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM #t'
EXEC sp_ExecuteSQL @stmt

FirstColumn SecondColumn ThirdColumn ForthColumn
----------- ------------ ----------- -----------
c1 c2 c3 c4

c1 c2 c3 c4
----- ----- ----- -----
a b c d

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 19, 2005

Is there any setting I have to enable?
Still I dont get any names

After
select @sql='Select '+substring(@sql,2,len(@sql))+' from #t'

@sql has "Select from #t"

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 19, 2005

I have an out-of-the-box installation. No special settings.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

FrankKalis · Aug 19, 2005

...but the INFORMATION_SCHEMA views always refer to the current database. That's why your solution won't work unless your in tempdb.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 19, 2005

quote:Originally posted by FrankKalis

...but the INFORMATION_SCHEMA views always refer to the current database. That's why your solution won't work unless your in tempdb.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thats correct
Thats why I used Use tempdb in my query

Why is it not possible to write tempdb..Information_schema.columns

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 19, 2005

Hm, I don't know. Frankly, I've never asked myself this question.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 19, 2005

I hope there is definitely a way to do this [<img src='/community/emoticons/emotion-1.gif' alt='' />] Madhivanan Failing to plan is Planning to fail

RGKN · Aug 19, 2005

I think it is possible you just need to miss one of the dots out as the table "owner" is Information_schema

Try

select * from tempdb.information_schema.columns

create table #rn
([tid] int identity(1,1),
[ttext] varchar(10))

select * from tempdb.information_schema.columns

Madhivanan · Aug 19, 2005

>>select * from tempdb.information_schema.columns

Well. Thats correct

But this does not return anything

select * from tempdb.information_schema.columns
where table_name like '%'+object_name(object_id('tempdb..#rn'))+'%'

Madhivanan

Failing to plan is Planning to fail

Adriaan · Aug 19, 2005

You need to get the column names from tempdb.dbo.syscolumns using the object id for the temporary table.

You can't use ...
OBJECT_NAME(tempdb.dbo.syscolumns.id) LIKE '#T%'
... because it will give you a NULL for temporary tables.

And you can't refer to tempdb.dbo.sysobjects, because there can be multiple temporary tables with the same base name but not necessarily the same column lists.

Luckily the id value in tempdb.dbo.syscolumns is the same as the OBJECT_ID() for the temporary table in your current database, so we can use this query:

USE Northwind
GO

CREATE TABLE #T (i INT, v VARCHAR(100))

SELECT S.[name] FROM tempdb.dbo.syscolumns S
WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')

DROP TABLE #T

FrankKalis · Aug 19, 2005

And then we arrive at a solution which doesn't use the INFORMATION_SCHEMA views anymore, but directly queries the system tables. I think Madhivanan was looking for such a way. Btw, in all the excitement, we should ask Ranjit whether his question had been answered or not. [<img src='/community/emoticons/emotion-1.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Madhivanan · Aug 19, 2005

Well Adriaan For temporary tables, it is better to use tempdb systables than views >>Btw, in all the excitement, we should ask Ranjit whether his question had been answered or not. I think he will be more clearer [<img src='/community/emoticons/emotion-1.gif' alt='' />] Madhivanan Failing to plan is Planning to fail

Adriaan · Aug 19, 2005

If they want to force us to use the INFORMATION_SCHEMA views, then they should add some that we can actually use.

Like permission listings per object per login/db_role, organized in columns. The current ones are sequential lists that you have to step through before you have any useful information.

Madhivanan · Aug 21, 2005

Ranjith, Did you get the solution?

Madhivanan

Failing to plan is Planning to fail

ranjitjain · Aug 22, 2005

Hi Madhivanan,
I have parked the log temporarily because of other bugs so i could not test it,
but i read all the posts for which i'm very thankful.
Ill implement each one by one and will keep you updated.
Thanx alot.

FrankKalis · Aug 23, 2005

Please do so. I would be interested in your final solution here. [<img src='/community/emoticons/emotion-1.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

ranjitjain · Aug 24, 2005

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan You need to get the column names from tempdb.dbo.syscolumns using the object id for the temporary table. You can't use ... OBJECT_NAME(tempdb.dbo.syscolumns.id) LIKE '#T%' ... because it will give you a NULL for temporary tables. And you can't refer to tempdb.dbo.sysobjects, because there can be multiple temporary tables with the same base name but not necessarily the same column lists. Luckily the id value in tempdb.dbo.syscolumns is the same as the OBJECT_ID() for the temporary table in your current database, so we can use this query: <pre id="code">USE Northwind GO CREATE TABLE #T (i INT, v VARCHAR(100)) SELECT S.[name] FROM tempdb.dbo.syscolumns S WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T') DROP TABLE #T</pre id="code"> <hr height="1" noshade id="quote"></blockquote id="quote"> Hi Adriann, I'm trying the above code to get column names with order by colid clause. But then it returns resultset with 4 rows so ill have to use cursor which i want to avoid. How to get all of them in a row???????[<img src='/community/emoticons/emotion-1.gif' alt='' />]

Madhivanan · Aug 24, 2005

No. It will work
Can you post the exact query you used?

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 24, 2005

Have you read the answers carefully? [<img src='/community/emoticons/emotion-5.gif' alt='' />] My suggestion returns one row querying the system tables, Madhivanan suggested aggregate concatenation. Combine this altogether and you arrive at something like this: <pre id="code"> USE Northwind GO DECLARE @list VARCHAR(100) CREATE TABLE #T (i INT, v VARCHAR(100)) INSERT INTO #T SELECT 1,'a' SELECT @list = ISNULL(@list + ',','') +S.[name] FROM tempdb.dbo.syscolumns S WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T') ORDER BY S.[colid] DESC SELECT @list EXEC ('SELECT '+ @list + ' FROM tempdb.dbo.#T') DROP TABLE #T </pre id="code"> -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Adriaan · Aug 24, 2005

The trick is that you can use SELECT for concatenating values into a variable, like this:
DECLARE @Cols VARCHAR(8000)

SET @Cols = ''

SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.name
FROM tempdb.dbo.syscolumns S
WHERE S.id = OBJECT_ID('tempdb.dbo.#T')
ORDER BY S.colid

FrankKalis · Aug 24, 2005

[<img src='/community/emoticons/emotion-2.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

ranjitjain · Aug 24, 2005

Hi,
i fired this

DECLARE @Cols VARCHAR(8000)
CREATE TABLE #T (i INT, v VARCHAR(100))
SELECT S.[name] FROM tempdb.dbo.syscolumns S
WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')

SET @Cols = ''
SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.[name]
FROM tempdb.dbo.syscolumns S
WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')
ORDER BY S.[colid]
select @cols
drop table #t

and the output is
name ----------------------------
i
v

--------------------------------------------------------
i, v

but i'm looking for something like this

col1 col2
i v

so instead of two rows one row with two cols....
else if i get i,v ill have to again split it.
As I want to show the result after that by the alias name so i need them in a row with n cols.

Adriaan · Aug 24, 2005

The reason why you're seeing two rowsets in the output is that your script now has two SELECTs returning a rowset (in bold):

DECLARE @Cols VARCHAR(8000)
CREATE TABLE #T (i INT, v VARCHAR(100))
SELECT S.[name] FROM tempdb.dbo.syscolumns S
WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')

SET @Cols = ''
SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.[name]
FROM tempdb.dbo.syscolumns S
WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')
ORDER BY S.[colid]
select @cols
drop table #t

Now you want to do cross-tab query on that - why?

Also, that's the point where a cursor is your only option, at least as long as (1) you're not using a client app that does the cross-tab for you, like Access, and (2) you're not using SQL Server 2005.

FrankKalis · Aug 24, 2005

You've missed the fact that you've need to execute this dynamically after you've retrieved the list of columns.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Madhivanan · Aug 24, 2005

Adriaan already answered this type of question from me
Refer this
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8291

Madhivanan

Failing to plan is Planning to fail

Adriaan · Aug 24, 2005

Madhivanan, thanks for dragging that out from the mists of time!

Madhivanan · Aug 24, 2005

No Problem Adriaan [<img src='/community/emoticons/emotion-1.gif' alt='' />] Madhivanan Failing to plan is Planning to fail

ranjitjain · Aug 24, 2005

Hi Adriaan, I know the fact why its returning two resultset. But the thing is when it returns all the columns names of that temp table in one column with 2-3 rows i need to use cursor which i want to avoid. So i thought it would be better if somehow i can get all column names in a row so that ill store them directly in 2-3 variables. So that the things for me will be easier and i can do something like . . . set @cmd=@col1val+' as cal_date,'+@col2val+' as isdata'+@col3val +' as iswe'. and finally ill fire select query with the above @cmd appended on that temp table.. I hope this idea is good for dealing to get the result of temp table.[<img src='/community/emoticons/emotion-1.gif' alt='' />]

ranjitjain · Aug 24, 2005

Hi Madhivanan,
I refered the link provided by you, and i got what i was expecting...
I think it will solve my problem somewhat.....

ranjitjain · Aug 25, 2005

Hi Guys,
I thought i got the solution by firing this...

CREATE TABLE #T (ispace INT, vautoid VARCHAR(100),z_123 int)
DECLARE @Test VARCHAR(8000)
declare @col1 varchar(50), @col2 varchar(50), @col3 varchar(50)
SET @Test = ''
SELECT @Test = @Test + CASE WHEN LEN(@Test) = 0 THEN '' ELSE ',' END + name
from tempdb.dbo.syscolumns WHERE id = object_id('tempdb.dbo.#T')
order by colorder

select @col1=left(@test,charindex(',',@test)-1)
select @col1
select @col2=substring(@test,charindex(',',@test)+1,charindex(',',@test,charindex(',',@test)+1)-charindex(',',@test)-1)
select @col2
select @col3=substring(@test,charindex(',',@test,charindex(',',@test)+1)+1,len(@test))
select @col3
drop table #t

But still solution is at par.
In the above example what modification i need to do if the temporary table name is dynamic, lets say something like...

declare @temptabname varchar(500)
select @temptabname= newid()
select @temptabname='[##'+@temptabname+']'
and this dynamic table name i want to use in above eg.?????

Madhivanan · Aug 25, 2005

This way you can do

Declare @tableName varchar(20)
Set @tableName='table'
Exec('Select Columns from '+@tableName)

Madhivanan

Failing to plan is Planning to fail

ranjitjain · Aug 25, 2005

Hi madhivanan,
Well thats true...
But as i mentioned in last msg, I'm creating dynamic named temp table and i'm happy with the above merged code to return columns of temp table where the #t is fixed and how can i replace that with my temp table name.
What modifications i need to do to add that table name to return column names in one row so that i can store them in variables to do further processing.

ranjitjain · Aug 25, 2005

Hi Frank,
I tried your solution.....which looks more better

CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c'
DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) ,
@b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) ,
@c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
FROMtempdb.dbo.syscolumns sc WHEREsc.[id] = OBJECT_ID('tempdb.dbo.#t')

SELECT @a , @b , @c
DECLARE @stmt NVARCHAR(4000)
SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM #t'
EXEC sp_ExecuteSQL @stmt
DROP TABLE #t

Now i just need suggestion on modification i need to do if the temp object name is dynamic.
lets say something like...

declare @temptabname varchar(500)
select @temptabname= newid()
select @temptabname='[##'+@temptabname+']'

ranjitjain · Aug 25, 2005

Hi Frank i modified the code and tried this but giving error......

DECLARE @stmt NVARCHAR(4000)
declare @tabname varchar(5)
set @tabname='#t'
CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c'
DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
set @stmt='SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
@b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
@c = MAX(CASE WHEN colorder=3 THEN sc.name END)
FROMtempdb.dbo.syscolumns sc WHEREsc.[id] = OBJECT_ID(''tempdb.dbo.'+@tabname+''')'
select @stmt
EXEC sp_ExecuteSQL @stmt
SELECT @a , @b , @c
set @stmt=''
SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
EXEC sp_ExecuteSQL @stmt
DROP TABLE #t

error....must declare the variable @a whats wrong in this code for dynamic building.

Madhivanan · Aug 25, 2005

You need to declare them inside @stmt
Try this

DECLARE @stmt NVARCHAR(4000)
declare @tabname varchar(5)
set @tabname='#t'
CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c'
--DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
set @stmt='
DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
@b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
@c = MAX(CASE WHEN colorder=3 THEN sc.name END)
FROM tempdb.dbo.syscolumns sc WHERE sc.[id] = OBJECT_ID(''tempdb.dbo.'+@tabname+''')
Select @a, @b, @c from '+@tabname+''
Exec(@stmt)
EXEC sp_ExecuteSQL @stmt
---SELECT @a , @b , @c
set @stmt=''
--SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
EXEC sp_ExecuteSQL @stmt
DROP TABLE #t

Madhivanan

Failing to plan is Planning to fail

ranjitjain · Aug 25, 2005

Hi madhivanan,
i tried your code but not getting what i'm expecting.
It returns the column names and the data from that table.
>When i comment the insert statement line it doesnt return anything.
>If i add another insert statement it repeats c1,c2,c3 twice.
But doen't display the data which is not the case when the temp table name is fixed.
I think some other modification is required.

FrankKalis · Aug 26, 2005

Ranjit, you missed the GROUP BY

SELECT
@a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END)
, @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END)
, @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
, @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)
FROM
tempdb..syscolumns sc
INNER JOIN
tempdb..sysobjects so
ON
so.id = sc.id
WHERE
so.type = 'u'
AND
so.id = OBJECT_ID('tempdb..#t')
GROUP BY so.name

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

ranjitjain · Aug 26, 2005

Hi Frank,
Do i still need group by clause as i modified with pripor suggestions and getting the column names but now my question is how can i change the script to include that dynamic temp table name????

ranjitjain · Aug 26, 2005

I think guys,
All the things have jumbled up and you all are not getting what i'm expecting.
Do you all want me to explain what i have upto and now what i want.
Please mention?????

Madhivanan · Aug 26, 2005

Yes that would be nice

Madhivanan

Failing to plan is Planning to fail

FrankKalis · Aug 26, 2005

Yes, definitely! [<img src='/community/emoticons/emotion-1.gif' alt='' />] -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

ghemant · Aug 28, 2005

Hi,
i have found article / blog on "Cross Tab Query - Generic works for any number of column and row"

http://www.browsedatabase.com/tutorial/tut13.html
http://www.msnusers.com/SQLBang/msgattachments/2

Regards

Hemantgiri S. Goswami
ghemant@gmail.com
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami

ranjitjain · Aug 28, 2005

Hi Guys,
Ill Start all over again.
I have a dynamic temp table name stored in a variable. The columns of that table is created in dll according to MDX query result returned.
So i want the column names of that temp objects so that i can show them using alias names which is necessary for presentation.
For e.g. the temp table can have product_category_327,time_quarter_jan,sales_amount named columns as the column names are also dynamic i can't fire select those coulmnnames from temp table.
I need those column names so that i can say select @col1name as prodname,@col2name as time_frame from @temptable.

So to achive this i tried adriaan and frank's code with slight modifications.
After using adriaan code which i posted what i used i stored the column names in variables with high use of left,substring,charindex functions.

So i went onto use Frank's code and i saved the result in diffrent variables.
In this code without using group by i'm getting the proper column names.

DECLARE @stmt NVARCHAR(4000)
declare @tabname varchar(5)
set @tabname='#t'
CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
INSERT INTO #t SELECT 'a', 'b', 'c'
INSERT INTO #t SELECT 'a', 'b', 'c'
DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
@b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
@c = MAX(CASE WHEN colorder=3 THEN sc.name END)
FROM tempdb.dbo.syscolumns sc WHERE sc.[id] = OBJECT_ID('tempdb.dbo.#t')
SELECT @a , @b , @c
set @stmt=''
SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
EXEC sp_ExecuteSQL @stmt
DROP TABLE #t

But now it doesn't work when i replace #t name which is fixed with @tabname(dynamic).
So what modifications i need to do in the following code.
When i follow madhivanans post to add declare statement of @a,@b inside '' string it doen't return proper result??????
I hope its clear what i'm upto now and what is my fresh issue.
Please ask if something is still uncleared.

Log in or Sign up

is this possible

ranjitjain New Member

FrankKalis Moderator

ranjitjain New Member

Madhivanan Moderator

ranjitjain New Member

FrankKalis Moderator

FrankKalis Moderator

Madhivanan Moderator

FrankKalis Moderator

Madhivanan Moderator

FrankKalis Moderator

Madhivanan Moderator

ranjitjain New Member

FrankKalis Moderator

Madhivanan Moderator

FrankKalis Moderator

Madhivanan Moderator

Adriaan New Member

FrankKalis Moderator

FrankKalis Moderator

FrankKalis Moderator

Madhivanan Moderator

FrankKalis Moderator

FrankKalis Moderator

Madhivanan Moderator

FrankKalis Moderator

Madhivanan Moderator

RGKN New Member

Madhivanan Moderator

Adriaan New Member

FrankKalis Moderator

Madhivanan Moderator

Adriaan New Member

Madhivanan Moderator

ranjitjain New Member

FrankKalis Moderator

ranjitjain New Member

Madhivanan Moderator

FrankKalis Moderator

Adriaan New Member

FrankKalis Moderator

ranjitjain New Member

Adriaan New Member

FrankKalis Moderator

Madhivanan Moderator

Adriaan New Member

Madhivanan Moderator

ranjitjain New Member

ranjitjain New Member

ranjitjain New Member

Madhivanan Moderator

ranjitjain New Member

ranjitjain New Member

ranjitjain New Member

Madhivanan Moderator

ranjitjain New Member

FrankKalis Moderator

ranjitjain New Member

ranjitjain New Member

Madhivanan Moderator

FrankKalis Moderator

ghemant Moderator

ranjitjain New Member

Share This Page