?
Solved

sql create table statement

Posted on 2011-04-30
3
Medium Priority
?
257 Views
Last Modified: 2012-05-11
Hi Expert,

I have student table,

Id Name

1 Mary  Lee
2 John   Stall
3 Marshall Young



How I can write a query to create a table use each record in Name as column names?
Table structure like this.

Id   [ Mary  Lee]   [John   Stall] [Marshall Young]

 because I have about 100 record in table, I need to create columns dynamic.

Thanks in advance.






0
Comment
Question by:dshi15
3 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 35498812
Why would you want to do that?

The table you have is properly structured as far as I can see.
0
 
LVL 1

Author Comment

by:dshi15
ID: 35498999
>>The table you have is properly structured as far as I can see.

I typed each column, I want generate dynamic, I need add count under each name later,

result like this


ProgramId  [ Mary  Lee]   [John   Stall] [Marshall Young]

1                      2                          3          2
2                      4                          5          5


I have hundred programs.
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35500303
Is this what you wanted?

-- You can run this code as one, it'll clean itself
USE tempdb;
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name='test_users')
DROP TABLE test_users
IF EXISTS (SELECT 1 FROM sys.tables WHERE name='test_dynamic')
DROP TABLE test_dynamic
--create test table
CREATE TABLE test_users
(id INT,
name VARCHAR(50));
GO

-- insert test table with some values
INSERT INTO test_users 
VALUES (1,'Mary Lee'),
(2,'John Stall'),
(3,'Marshall Young'),
(4,'Test User1'),
(4,'Test User2'),
(4,'Test User3');
GO

-- build and execute dynamic sql command to create table
-- I assumed that ProgramId is first column regardless of other values in test table
DECLARE @sql VARCHAR(8000);
DECLARE @name VARCHAR(50);
DECLARE @val VARCHAR(10);

SELECT @val = ' INT, ';
SELECT @sql='CREATE TABLE test_dynamic (ProgramId INT,';

DECLARE Cur CURSOR FOR
SELECT name FROM test_users;

OPEN Cur;
FETCH NEXT FROM Cur INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN  
	SELECT @sql=@sql+'['+@name+']'+@val;
	FETCH NEXT FROM Cur INTO @name;
END
SELECT @sql=SUBSTRING(@sql,0,LEN(@sql))+');';
SELECT @sql AS sql_to_create_table;
EXEC (@sql);
CLOSE Cur;
DEALLOCATE Cur;
GO
-- show column names and values types in new dynamically created table
SELECT sc.name,st.name FROM sys.columns sc JOIN sys.types st
ON sc.system_type_id=st.system_type_id WHERE sc.[object_id]=object_id('test_dynamic');
GO
--drop test and dynamic tables
DROP TABLE test_users;
GO
DROP TABLE test_dynamic;
GO

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question