1

I am working on a reporting project based in SQL but I have restricted access to the DB; I can only make SELECT Queries and insert the data I retrieve into temp Tables/table variables. I cannot create/execute stored procedures or any sort of functions.

The query I am running is meant to pool together all Engineers and the different key skills that they have so that we can later on see what Skills each engineer has or which Engineers fall under a certain skill.

To this end, I am trying to create a table variable/temp table with a flexible structure, a structure based on previously obtained values in the same query.

For E.g.

1st Output: Adam Brad Julio Martinez

2nd Output (Skill separated by white space): VOIP TTS DBA Exchange Server

Create temp table/table variable that uses 1st output as rows and 2nd output as columns or vice versa. I will then populate this new table according to different values on the main DB.

Please advise how this can be done, or provide any other solution to this problem. Thank you

UbiNoob
  • 11
  • 4

2 Answers2

2

I believe you can.

First of all you need to create temp table with dynamic structure based on query. It can be done like this:

declare script template:

Set @ScriptTmpl = 'Alter table #tempTable Add [?] varchar(100);

build script that will insert columns you need based on query:

Select @TableScript = @TableScript + Replace(@ScriptTmpl, '?', ColumnName) From ... Where ...

then execute script and then fill your new table with values from second query

UPD:

here is the full sample of temporary table dynamic creation. I used global temporary table in my sample:

declare @scriptTemplate nvarchar(MAX)
declare @script nvarchar(MAX)
declare @tableTemplate nvarchar(MAX)

SET @tableTemplate = 'create table ##tmptable (?)'
SET @scriptTemplate = '? nvarchar(500),'
SET @script = ''

Drop table ##tmptable

Select @script = @script + Replace(@scriptTemplate, '?', [Name])
From Account
Where name like 'ES_%'

SET @script = LEFT(@script, LEN(@script) - 1)

SET @script = Replace(@tableTemplate, '?', @script)

Select @script

exec(@script)

Select * from ##tmptable
Greenonion
  • 87
  • 5
  • I couldn't quite figure out how to use this method; could you provide a more elaborate example if you don't mind or maybe a link that would explain it a bit more? Thanks – UbiNoob Aug 22 '14 at 10:27
  • Thanks. I'm going to try this out. – UbiNoob Aug 22 '14 at 11:12
  • Hoo Lord, just had a SQL-Gasm. Thanks, this worked well. For anyone who will use this later:It won't work with Table Variable/Temp Table and make sure there is no whitespace in the values you are using to create the new table (yes this maybe be obvious) – UbiNoob Aug 22 '14 at 14:51
0

Firstly, you may be able to achieve what you want through pivots, rather than temporary tables.

Secondly, if you really want to create a table with column name "Adam Brad", the solution is dynamic SQL, which you may not be able to do based on your permissions.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • I tried pivots but doesn't exactly do what I need; Yeah, permission restrictions just breaks my back. – UbiNoob Aug 22 '14 at 10:25