Dynamic Table Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Table Select

Author

Topic

wbcintsol
Starting Member

4 Posts

Posted - 2007-10-03 : 21:54:07

Hi guys,
I have a product table that is being build dynamically.
I have 10 text columns (TextField1,TextField2, etc.)
10 int columns
10 decimal columns
And so on.
When you create a product - you decide how many text columns you have and they associated name for the column and so on.

My problem is - I need to display each product by id, but only display the columns that actually have value in them,
So just 2 text columns, 3 int columns and 4 decimal columns.

How do I write a query like that?

Thanks for the help!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-03 : 22:12:58

I think you are only going to be causing future issues with this method.

Look into creating the table more along the lines of

Table1(Productid)
Table2(ProductID,TypeID,Description)

Then you have unlimited scalability and a better db layout.

wbcintsol
Starting Member

4 Posts

Posted - 2007-10-03 : 22:15:15

I can't...
There are too many products, each product in need for different columns (names and values)
That's the best solution for me at the moment

Thanks for your reply though ;)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-03 : 22:21:17

by text columns you mean datatype text or varchar ?

"but only display the columns that actually have value in them"
does all the records have the same number of columns with value in it ?

Can you post some sample data and the required result ?

KH
[spoiler]Time is always against us[/spoiler]

wbcintsol
Starting Member

4 Posts

Posted - 2007-10-04 : 00:22:42

Thanks for your reply Vinnie.,

Here is a my table, maybe that would make a little bit more sense




CREATE TABLE [dbo].[Product](
	[Product_Id] [int] IDENTITY(1,1) NOT NULL,
	[Product_Group_Id] [int] NOT NULL,
	[Product_Name] [nchar](50) NOT NULL,
	[TextField1] [nchar](250) NULL,
	[TextField2] [nchar](250) NULL,
	[TextField3] [nchar](250) NULL,
	[TextField4] [nchar](250) NULL,
	[TextField5] [nchar](250) NULL,
	[TextField6] [nchar](250) NULL,
	[TextField7] [nchar](250) NULL,
	[TextField8] [nchar](250) NULL,
	[TextField9] [nchar](250) NULL,
	[TextField10] [nchar](250) NULL,
	[IntField1] [int] NULL,
	[IntField2] [int] NULL,
	[IntField3] [int] NULL,
	[IntField4] [int] NULL,
	[IntField5] [int] NULL,
	[IntField6] [int] NULL,
	[IntField7] [int] NULL,
	[IntField8] [int] NULL,
	[IntField9] [int] NULL,
	[IntField10] [int] NULL,
	[DecimalField1] [decimal](18, 2) NULL,
	[DecimalField2] [decimal](18, 2) NULL,
	[DecimalField3] [decimal](18, 2) NULL,
	[DecimalField4] [decimal](18, 2) NULL,
	[DecimalField5] [decimal](18, 2) NULL,
	[DecimalField6] [decimal](18, 2) NULL,
	[DecimalField7] [decimal](18, 2) NULL,
	[DecimalField8] [decimal](18, 2) NULL,
	[DecimalField9] [decimal](18, 2) NULL,
	[DecimalField10] [decimal](18, 2) NULL,
	[MultyLineField1] [ntext] NULL,
	[MultyLineField2] [ntext] NULL,
	[MultyLineField3] [ntext] NULL,
	[MultyLineField4] [ntext] NULL,
	[MultyLineField5] [ntext] NULL,
	[MultyLineField6] [ntext] NULL,
	[MultyLineField7] [ntext] NULL,
	[MultyLineField8] [ntext] NULL,
	[MultyLineField9] [ntext] NULL,
	[MultyLineField10] [ntext] NULL,

And example of data inside the table is: (and I'm not using all columns)

Prod Id Prod Name group Id text1 text2 text3 int1 int2

1 Prod1 23 File 546

2 Prod2 8 Path User Add

Does this clear a bit up?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources