March 6, 2015 at 3:36 am
Hi to every ones,
I am new to this forum and to SQL world.
I need your help to find a solution for my headache, my question is:
I am using SQL SERVER 2012
I have the following table:
tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |
I want to creare a new empy table with the columns name contained into the column B of the first table; the following shold my results:
tbtab2
| Pluto | Pippo | Rossi |
Colud you hel on this task?
Thanks
March 6, 2015 at 3:40 am
rolinoberto (3/6/2015)
Hi to every ones,I am new to this forum and to SQL world.
I need your help to find a solution for my headache, my question is:
I am using SQL SERVER 2012
I have the following table:
tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |
I want to creare a new empy table with the columns name contained into the column B of the first table; the following shold my results:
tbtab2
| Pluto | Pippo | Rossi |
Colud you hel on this task?
Thanks
What should be the data types (varchar, int, bit etc) of the columns?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2015 at 4:59 am
The tbtab1 column B is TEXT
The new tbtab2 columns should be INT
March 6, 2015 at 5:18 am
See if this gives you what you need.
if object_id('dbo.tab1', 'U') is not null
drop table dbo.tab1
create table dbo.tab1
(
a int primary key
,b text
)
insert dbo.tab1
(a, b)
values (1, 'Pluto'),
(2, 'Pippo'),
(3, 'Rossi')
select *
from dbo.tab1 t
declare @sql varchar(max) = '';
select @sql = concat(@sql, ',', b, ' int')
from dbo.tab1 t
set @sql = stuff(@sql, 1, 1, ' create table dbo.tab2 (') + ')'
select @sql
Nothing is created here, but @sql should contain the CREATE TABLE syntax you need.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2015 at 5:49 am
It is working fine, thanks.
now, my dubit is: how to use @sql content to create the new table?
Could you suggest some idea?
March 6, 2015 at 5:57 am
Sure. Copy the text from the results grid, paste it into SSMS and execute it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2015 at 6:10 am
Sorry, I means automatically within an application (Visual Basic 2012)
suppose I have an application, which is used to fill and use the information stored in a database, suppose (after a couple of months/later on) I add a new record into the tbtab1, which action need to be followed by adding the new row as a new column into the tbtab2.
Suppose I have a button into my application which is responsible to create (the first time I use the application) and update (all other next times) the new table tbtab2, how I can include the content of @sql into a stored procedure, for example, which can be called by the visual basic button?
Opfully my explanation is clear, it is not simple to show my idea
March 6, 2015 at 6:18 am
rolinoberto (3/6/2015)
Sorry, I means automatically within an application (Visual Basic 2012)suppose I have an application, which is used to fill and use the information stored in a database, suppose (after a couple of months/later on) I add a new record into the tbtab1, which action need to be followed by adding the new row as a new column into the tbtab2.
Suppose I have a button into my application which is responsible to create (the first time I use the application) and update (all other next times) the new table tbtab2, how I can include the content of @sql into a stored procedure, for example, which can be called by the visual basic button?
Opfully my explanation is clear, it is not simple to show my idea
Exec (@sql) will execute the code. However, this will work only once (when the table does not exist).
If you want to add another column (eg, Dumbo) to the table after it has been created, you'll need something like:
alter table dbo.tab2
add Dumbo int
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2015 at 6:51 am
By chance, is there any reasonable limit of number of TBTAB2 columns needed for the period of the application life ?
Suppose max is 25 columns. Then create a table with max number of columns
CREATE TABLE zTBTAB2 (
i01 INT NULL,
...
i25 INT NULL)
and recreate VIEW TBTAB2 along with updating TBTAB1 in the application.
March 6, 2015 at 8:51 am
If I add the following:
SELECT c.name AS ExistingColOnTab2
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.tab2')
GO
it shows the column's name already exist in the "tab2" table.
There is a way to join the result of code abow in order to execute the following line in the new temporary table, which should be containing the name string exept the existing ones.
DECLARE @C_additional varchar(max) = '';
SELECT @C_additional= CONCAT(@C_additional, ',', <NewColumnTemporaryTable>, ' int')
FROM dbo.temporarytable
set @C_additional = stuff(@C_additional, 1, 1, ' alter table dbo.tab2 add ')t
I am sorry in advance if I make a lot of confusion
March 6, 2015 at 10:17 am
It has to be said: this is a very weird requirement. I would be interested to know why you are doing things this way – are you able to provide some background? But here goes ...
Building on my earlier code:
if object_id('dbo.tab1', 'U') is not null
drop table dbo.tab1;
if object_id('dbo.tab2', 'U') is not null
drop table dbo.tab2;
create table dbo.tab1
(
a int primary key
,b text
);
insert dbo.tab1
(a, b)
values (1, 'Pluto'),
(2, 'Pippo'),
(3, 'Rossi');
select *
from dbo.tab1 t;
declare @sql nvarchar(max) = '';
select @sql = concat(@sql, ',', b, ' int')
from dbo.tab1 t;
set @sql = stuff(@sql, 1, 1, ' create table dbo.tab2 (') + ')';
exec sys.sp_executesql
@sql;
--Add another row to tab1
insert dbo.tab1
(a, b)
values (4, 'Spock');
--Create the ALTER statement
set @sql = '';
select @sql = concat(@sql, ',', b, ' int')
from dbo.tab1 t
where not exists ( select 1
from sys.columns c
where c.object_id = object_id('dbo.tab2')
and c.name = cast(t.b as sysname) );
set @sql = stuff(@sql, 1, 1, 'alter table dbo.tab2 add ');
--Execute the ALTER
exec sys.sp_executesql
@sql;
select *
from dbo.tab2;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2015 at 12:59 pm
rolinoberto (3/6/2015)
suppose I have an application, which is used to fill and use the information stored in a database, suppose (after a couple of months/later on) I add a new record into the tbtab1, which action need to be followed by adding the new row as a new column into the tbtab2.
And what is going to happen if in a few years there are hundreds or thousands of rows that would need to be made into columns? I would think again about this design if I were you. You could be setting someone up for some major headaches in the future. Unless, of course, your business rules dictate there will never be more than a few rows needing to be transposed. But then again, those rules might change after you've moved on. Just my two cents.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 6, 2015 at 11:58 pm
First of all thanks ...
I have no problems to explain my intention. Hopefully you can continue to make suggestions.
I'm build the application using Visual Basic, myself. The new application is going to replace an excel format that we use for many years, which is filled with thousands of lines, but has just few columns. In the past it happened that we were forced to add 6 more columns into the excel table. It was easy, but ... what if we have to do the same with the new application?
The next step, once the new application is almost complete and installed in a PC for a test, will be to import all records from the Excel file, so we will have the story available in the new database.
The second reason to implement a stored procedure like this, is the reusability of this application to a different place, where the information to be stored are similar to mine, but the name of the columns should be different (in this case changing the rows into the fisrst table, only and using the <configuration button> will be easy to adapt the same application and database for a different place.
I hope that my colleagues will be happy to throw the excel file and use a real database system!
To imports the excel file to the new database, I know the existence of SQL BULK ... but that will be another story!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply