May 26, 2013 at 10:41 pm
Hi all,
I have a table with 2 columns named Depot_Id & Depot_Name, i have to make another table from the values inserted into Depot_Name as the columns of new table.
I dont have any idea for that...
kindly suggest me solution for this.
this is Depot Table:-
Depot_Id Depot_Name
1 A
2 B
3 C
Now i need another table in which column name should b A,B and C..
May 27, 2013 at 2:24 am
It is quite easy to do this manually.
What is the problem you are facing in doing this?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 2:50 am
i have to do it dynamically..
May 27, 2013 at 3:48 am
abhishekagrwl25 (5/27/2013)
i have to do it dynamically..
you can make use of PIVOT for this..........
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 4:14 am
DECLARE@strSQL VARCHAR(1000)
SELECT@strSQL = COALESCE(@strSQL + ',', '')
+ '[' + Depot_Name + '] VARCHAR(100)' + CHAR(13) -- I assumed the column data type to be VARCHAR
FROMDepot
SET@strSQL = ' CREATE TABLE AnotherTable' -- Your table name here
+ ' ( '
+ @strSQL
+ ' ) '
EXECUTE (@strSQL)
Why do you want to do it dynamically? Any specific reason?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 4:19 am
because data in the column Depot_Name will be filled by user...it can b delete also...
and i also want to show the Depot_Id as the value of the columns...
May 27, 2013 at 4:22 am
create table #table (Depot_Id int , Depot_Name varchar(10))
insert into #table
Select '1','A' union
Select '2' ,'B' union
Select '3', 'C'
Select * FRom
(
select Depot_Id , Depot_Name From #table
)V PIVOT
(
max (Depot_Id) FOR Depot_Name in ([A], ,[C])
) as PVT
Neeraj Prasad Sharma
Sql Server Tutorials
May 27, 2013 at 4:46 am
if there are 3rd column in table named Depot_Supply...
and i also want insert the value of this column into new table....pivot operator can only be used for one column...but i also have to show the Depot_Supply...
May 27, 2013 at 4:50 am
abhishekagrwl25 (5/27/2013)
because data in the column Depot_Name will be filled by user...it can b delete also...and i also want to show the Depot_Id as the value of the columns...
In that case, you need not create another table
You can use CROSS-TABS or PIVOT as mentioned by the above poster
The below given links are links to articles by Jeff Moden which will help you create a query for this
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply