Creating a table from the Rows of Other table

How can I create table from the rows of other table?

My requirement is I have a table test which has a column Abc with some values say a,b,c,d,e

Is it possible to create a table which has the column names as a,b,c,d,e...

Since the rows in the test table are dynamic...is it possible to create a table with the dynamic columns?

Any pointers in this regard?

Yes.. You can do this. Use INTO clause on your Select Statement.

Select A,B,C,D,E INTO NEWTABLE from ABC

|||

Thanks Sekaran.

But my problem is I am not sure the number of rows in my first table.

i,e if I do Select * from temp and it it returns 10 rows then those 10 rows should be the column names in my second table.and if there are only 5 rows then my second table should have 5 columns only.

|||

Ok.. You want to create table using your Rows..

I am not sure why you need this.. This is not good idea to create a table on the fly.

Are you want to convert the Row wise data into column? Something like PIVOT table.

Give more info...

|||

tried with PIVOT it doesnt seem to work out .. as i dont have an INTEGER on which i can pivot

and i dont know what would my for() will have.

see this is my case:

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in b1 always vary)

select a1,a2,a3 from A

pivot

max(?)

for ([?],[?].....)

order by ?

and above all.....I'm just trying to create the schema and surely not going for the population at this moment

awaiting for your quick reply

|||

I've a table A with columns a1,a2,a3

i've table B with column b1 and values a4,a5 (offcourse the number of rows in B always vary)

and my resulting table C should be having a1,a2,a3,a4,a5 columns

|||

If I understand you want the table to have the same schema as table A but in addition to also have the data values in table B as additional columns?

The only way I can think is to create the table using dynamic sql using syscolumns to generate the first part of the SQL and then cursoring through the datavalues in table be to generate the remaining SQL.

I'm not sure why you would do this but if you need to then I suggest that you strictly control the entries in table B.

|||

Thanks Sunny,

But there is no way that I can restrict the entries in table B. But at the max there will be 30-40 rows which needs to be changed as the header for other report.

Can trigger help me in my case?

|||

Still your problem is not clear, help us understand in better way.

Pls put some proper sample data rather A,B,C & a1, a2 ...

Its confusing buddy..

|||

Here is the example code:

Create table Meta(Columnname varchar(20))

Insert into Meta values('EmpNo')

Insert into Meta values('EmpName')

Insert into Meta values('Address')

Now if I do select * from Meta the result will be

EmpNo

EmpName

Address

Then I have another table Employee with two columns(Tel# ,SSN)

So my requirement is to change the schema of Employee table as (EmpNo,EmpName,Address,Tel#,SSN)

This is just an example as the number of rows in the Meta table is not known.

|||

Ok. You want to change the database (table) structure when you insert any new column on META table.

I don't recommand this. This is not a good practice at all.

If there is any schema change it should be done via proper script & by one hand(most of the time DBA).

I am really not sure why this dangerous logic you took in your hand.

If you ask me strightly its possible to do via trigger... But take care, take care on Update/Delete of your META data.

You may mess-up lot of dependent SPs, Views, Functions & even on your UI .

Create Trigger Meta_Trigger On Meta

For Insert

as

Begin

Declare @.Q as varchar(1000);

Select @.Q ='Alter Table Employee Add ' + ColumnName + ' Varchar(1000)' From Inserted;

Exec(@.Q)

End

|||

sekharan....how will i get the "columnname" in the above case

my problem was always getting the variable name here!!!

|||Whenever you insert the new column from your variable to the Meta table the trigger will find the newly inserted value using the INSERTED spl table...

Creating a range lookup table from a file of millions of rows

I have a file with the item id and the item type. The data looks as follows
:
ItemID ItemType
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 A
9 A
I want to create a lookup tables as follows:
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
Please keep in mind the file I have the ids on is millions of rows. Also,
there are gaps in the ids, (i.e. may jump from 4 to 6 no 5). Gaps are
acceptable as long as they are not too large.
Thanks in advance for any tips you can provide.I'm not sure what you mean by "gaps are acceptable as long as they are
not too large". Apparently no gaps in your sample data anyway. See if
this meets your requirements:
SELECT MIN(itemid), MAX(itemid), itemtype
FROM
(SELECT T1.itemid, T1.itemtype,
MIN(T2.itemid) AS x_itemid
FROM tbl AS T1
LEFT JOIN tbl AS T2
ON T1.itemtype <> T2.itemtype
AND T1.itemid < T2.itemid
GROUP BY T1.itemid, T1.itemtype) AS T
GROUP BY itemtype, x_itemid
If performance is an issue then you could do this for smaller subsets
of rows and then combine the results.
David Portas
SQL Server MVP
--|||David
Can I ask you, why did you join the table?
create table #test
(
itemid int not null primary key,
itemtype char(1) not null
)
insert into #test values (1,'a')
insert into #test values (2,'a')
insert into #test values (3,'a')
insert into #test values (4,'b')
insert into #test values (5,'b')
insert into #test values (6,'c')
insert into #test values (7,'c')
insert into #test values (8,'d')
select min(itemid),max(itemid),itemtype
from #test group by itemtype
What is differ between these queries?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123000386.349876.22890@.g47g2000cwa.googlegroups.com...
> I'm not sure what you mean by "gaps are acceptable as long as they are
> not too large". Apparently no gaps in your sample data anyway. See if
> this meets your requirements:
> SELECT MIN(itemid), MAX(itemid), itemtype
> FROM
> (SELECT T1.itemid, T1.itemtype,
> MIN(T2.itemid) AS x_itemid
> FROM tbl AS T1
> LEFT JOIN tbl AS T2
> ON T1.itemtype <> T2.itemtype
> AND T1.itemid < T2.itemid
> GROUP BY T1.itemid, T1.itemtype) AS T
> GROUP BY itemtype, x_itemid
> If performance is an issue then you could do this for smaller subsets
> of rows and then combine the results.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Uri,
Replace
insert into #test values (8,'d')
with
insert into #test values (8,'a')
and see the difference
With warm regards
Jatinder Singh|||So David's script gave me a wrong output.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123050090.479481.14310@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Replace
> insert into #test values (8,'d')
> with
> insert into #test values (8,'a')
> and see the difference
> With warm regards
> Jatinder Singh
>|||Hi Uri,
It gave correct ouput to me.
Start End ItemType
8 9 A -- (1)
1 3 A -- (2)
4 5 B
6 7 C
The only thing is (1) appears at top which can be easily adjusted by
using ored by clause
With warm regards
Jatinder Singh|||Hi
Should not be 1 for MIN and 9 for MAX for A?
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123056485.365685.300130@.g14g2000cwa.googlegroups.com...
> Hi Uri,
> It gave correct ouput to me.
> Start End ItemType
> 8 9 A -- (1)
> 1 3 A -- (2)
> 4 5 B
> 6 7 C
> The only thing is (1) appears at top which can be easily adjusted by
> using ored by clause
> With warm regards
> Jatinder Singh
>|||Hi Uri,
Again Let us see this
ItemID ItemType
1 A -- *
2 A -- * One Group with ItemType='a' Here
min(itemid)= 1 and max is 3
3 A -- * 1 3 A (One Row of Reuired Result)
4 B -- ^ Another Group with ItemType='b' Here
min(itemid)= 4 and max is 5
5 B -- ^ 4 5 B (Another Row of Reuired Result)
6 C -- ~Another Group with ItemType='c' Here
min(itemid)= 6 and max is 7 7 C -- 6 7 C
(Another Row of Reuired Result)
8 A -- Again 'A' is repeated but there is gap so it
is to be considerd as a
9 A -- Fresh Group
-- 8 9 A
So the resultant output produced by David's Query is Correct
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
I hope it made the author's requirements more clear.
With warm regards
Jatinder Singh|||The difference is that your query only gives one row per ItemType
rather than one row per contiguous sequence on ItemType. I call my
query a "condensed" or "stepped" sequence rather than an aggregation.
The point is that it shows the regions or periods over which a
particuar ItemType applies. In my interpretation that's what BTJ was
asking for.
David Portas
SQL Server MVP
--|||David
Thanks, I got it
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123058956.842173.89690@.o13g2000cwo.googlegroups.com...
> The difference is that your query only gives one row per ItemType
> rather than one row per contiguous sequence on ItemType. I call my
> query a "condensed" or "stepped" sequence rather than an aggregation.
> The point is that it shows the regions or periods over which a
> particuar ItemType applies. In my interpretation that's what BTJ was
> asking for.
> --
> David Portas
> SQL Server MVP
> --
>

Creating a cube...and some ?s

1. Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.

2. Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a. IS NULL-
b. GROUPING-
c. AS-

3. Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.

4. Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a. A CUBE query with Grouping used to distinguish Null values.
b. A CUBE query showing a multidimensional cube.
c. A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.

5. Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.I have no clue how to do this|||Sounds Like Homework to me .

Better gear unp for some lashing from sundialsvcs (http://www.dbforums.com/showthread.php?threadid=979777)

Creating a blank table from another

Hi again,

I want to create a blank table with the same column names as another but with no rows i.e. it is empty. Does anyone know how to do the last part?

I have so far

create table newtest as select * from shared.test .... (empty bit here?)

Any good advanced sql tutorial urls would be good to.

Thanks in advance :)select *
from OldTable
into Newtable
where 1 = 0

This will not copy triggers, indexes, and the such. Just column names and datatypes.|||Ok i will try that but why does that work?

Cheers :)|||select *
from OldTable
into Newtable

...selects columns from the old table into a Newtable (created on the fly).

where 1 = 0

...always evaluates to false (in my universe, but I'm a Democrat), so no rows match the filter criteria and thus no rows are actually inserted.|||Cheers, Thanks

:D|||U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...|||if you need to copy the table as is (with all indexes, primary/foreign keys, triggers), you can use the option "generate sql script":

Enterprise Manager > Server > DataBase > Tables > put your cursor on the selected table/s > All Tasks (right mouse click) > Generate SQL Script

Then you get a wizard which allow you to create scripts that drops/create your selected objects. on the options tab you may decide if you would like to have the indexes, triggers, relationships...

If you want to create the same table BUT with different name, make sure you rename all the objects (indexes, primary/foreign keys, triggers) before running.

good luck|||Originally posted by Sowmyam
U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...

Ahhhh...the smell of Oracle (Or is it UDB) in the morning...it smells like....confusion

USE Northwind
GO

CREATE TABLE myTable99
AS
SELECT * FROM Orders
WHERE rownum < 1;
GO


And nope...that won't work...|||Hi Brett,
I didn't get u clearly, are u telling

create table NewTable
as select * from OldTable
where rownum < 1;

this code won't work.

I tried it works.
Ok this query Creates a Table NewTable of same structure as OldTable and NewTable will not have any rows.
This won't copy any indxes, constraints and all, but creates a table.

pls give a feedback for this.

Thanks In Advance.|||Hi Brett,
I am very sorry. yes it is confusion.
it will work in Oracle, this is Microsoft SQL server, by thinking it is Oracle i replied.

I am very sorry about it.|||hi
just add truncate command after your code!!

hope this will solve ur issue.

Cheers

Deepak K