Table Colmn Names

Home ›

ASP.NET Forums » Data Access » SQL Server, SQL Server Express, and SqlDataSource Control » Table Colmn Names

Shortcuts

Previous Next

Last post 07-15-2009 11:23 AM by DerickC. 5 replies.

Sort Posts:

Table Colmn Names

07-07-2009, 8:31 AM

Contact

46 point Member
DerickC
Member since 08-14-2007, 12:39 AM
South Africa
Posts 90

Hi All,

The database I need to get info from has a table with about 60 columns (tblCourses). In the data it is true or false. This indicates the app if the learner should do the specific course which the column name present. I know it is poor table design but I need to extract info from it. I need to list the column names and indicate which are true, or even better filter only the true values. I used this in excel and I think I used Transpose. I would appreciate it if somebody can help me into the right direction.

I need somthing like below but only for the columns with a value on 1

Course:
*******
DoCourse1Mod1
DoCourse1Mod2
DoCourse1Mod3

Please see table below, and first row below that

USE [File]
GO
/****** Object: Table [dbo].[SF_LearnDesignation] Script Date: 07/07/2009 11:59:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SF_LearnDesignation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LearnDesignation] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[LearnCode] [int] NULL,
[GenCode] [int] NULL,
[DoCourseG01] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod9] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse1Mod10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG02] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse2Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG03] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse3Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse3Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse3Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse3Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse3Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG04] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse4Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG05] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse5Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse5Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse5Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse5Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse5Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG06] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse6Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse6Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse6Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse6Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse6Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG07] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse7Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse7Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse7Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse7Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse7Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG08] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse8Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse8Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse8Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse8Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse8Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG09] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod9] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod11] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod12] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse9Mod13] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod14] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod15] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod16] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod17] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod18] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse10Mod19] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG11] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod20] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod21] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod22] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod23] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod24] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod25] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod26] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod27] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod28] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod29] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod30] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod31] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod141] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod32] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod33] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod34] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod35] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod36] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod37] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod38] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod39] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod40] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod41] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod42] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod43] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod44] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod45] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod46] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod47] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod48] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod49] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod50] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod101] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod102] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod129] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod130] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod143] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod144] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG12] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod51] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod52] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod53] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod54] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod55] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod56] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod57] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod58] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod59] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod60] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod126] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod145] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse12Mod150] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG13] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod61] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod62] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod63] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod64] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod65] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod66] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod67] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod68] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod69] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod70] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod71] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod72] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod73] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod74] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod75] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod76] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse13Mod77] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG14] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod78] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod79] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod80] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod81] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod82] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod83] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod124] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse14Mod125] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG15] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod84] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod85] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod86] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod87] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod88] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod89] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod90] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod91] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod92] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod127] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod128] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod139] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod140] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse15Mod142] [char](10) COLLATE Latin1_General_CI_AI NULL,
[DoCourse15Mod151] [char](10) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_SF_LearnDesignation_DoCourse15Mod151] DEFAULT ((0)),
[DoCourseG16] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod93] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod94] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod95] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod96] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod97] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod98] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod99] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse16Mod100] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG17] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod107] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod108] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod110] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod111] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod112] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod113] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod114] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod115] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod116] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod117] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod118] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod119] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod120] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod121] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod122] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse17Mod123] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG18] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse18Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG19] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse19Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse19Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse19Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG20] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse20Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse20Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse20Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG21] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse21Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse21Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse21Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG22] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse22Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG23] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse23Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse23Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse23Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG24] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse24Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse24Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse24Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG25] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse25Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse25Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse25Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG26] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse26Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse26Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse26Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse26Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse26Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG27] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse27Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse27Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse27Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG28] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod131] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod132] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod133] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod134] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod135] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse28Mod136] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG29] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod9] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod11] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod12] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod13] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod14] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse29Mod15] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG30] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod9] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod11] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod12] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod13] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod14] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod15] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod16] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse30Mod17] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourseG32] [char](10) COLLATE Latin1_General_CI_AI NULL,
[DoCourse32Mod148] [char](10) COLLATE Latin1_General_CI_AI NULL,
[DoCourseG33] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod2] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod3] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod4] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod5] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod6] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod7] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod8] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod9] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod10] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod11] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse33Mod12] [char](10) COLLATE Latin1_General_CI_AS NULL,
[DoCourse11Mod146] [char](10) COLLATE Latin1_General_CI_AS NULL,
[Electrical] [char](10) COLLATE Latin1_General_CI_AS NULL,
[Mining] [char](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

***************************************************************
Data
***************************************************************
2646 i- Stope cleaner 1,3,8# NULL NULL 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 1 1 1 1 0 0 0 0 1 1 1 1 1 1 1 1 1 0 0 0 0 1 1 1 1 1 1 0 0 0 0 0 0 1 1 0 0 0 0 0 1 1 1 1 1 1 1 0 0 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 1 1 0 1 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Thanks

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Re: Table Colmn Names

07-07-2009, 8:53 AM

Contact

8,529 point Star
Steelymar
Member since 01-28-2009, 9:39 AM
Bulgaria
Posts 1,259

Use UNPIVOT relational operator:

select LearnCode,DoCourse,Val from
(select DoCourseG01,DoCourse1Mod1,......,DoCourse11Mod146 from SF_LearnDesignation ) p
UNPIVOT
(Val FOR DoCourse in
(DoCourse1Mod1,...,DoCourse11Mod30,...,DoCourse11Mod146) )AS unpvt;

http://technet.microsoft.com/en-us/library/ms177410.aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Regards,
Stefan Uzunov

Re: Table Colmn Names

07-07-2009, 9:26 AM

Contact

46 point Member
DerickC
Member since 08-14-2007, 12:39 AM
South Africa
Posts 90

Thanks for the reply, is there no way of not specifying the col names, the col names are added with out warning and then this report/page will get a error

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Re: Table Colmn Names

07-07-2009, 10:00 AM

Contact

8,529 point Star
Steelymar
Member since 01-28-2009, 9:39 AM
Bulgaria
Posts 1,259

ok....

use somthing like this:

declare @t as table (C_name varchar(500),val bit)
DECLARE @C_name as Varchar(500)
DECLARE _cursor CURSOR FOR
select column_name
from information_schema.columns
where table_name = 'SF_LearnDesignation'

OPEN _cursor
FETCH NEXT FROM _cursor INTO @C_name

WHILE @@FETCH_STATUS = 0
BEGIN

exec( 'insert into @t select '''+@C_name +''', '+@C_name+' from SF_LearnDesignation')

FETCH NEXT FROM _cursor INTO @C_name
END
CLOSE _cursor
DEALLOCATE _cursor

select * from @t

I can't test it but must work...

Regards,
Stefan Uzunov

Re: Table Colmn Names

07-07-2009, 10:35 AM

Contact

Answer

3,878 point Contributor
gmmastros
Member since 01-13-2009, 2:01 PM
Near Philadelphia, PA
Posts 594

You could also try this:

Declare @SQL VarChar(Max)

Set @SQL = ''
Select	@SQL = @SQL + ' Select ID, ''' 
        + Column_Name 
        + ''' As Course From SF_LearnDesignation Where [' 
        + Column_Name 
        + '] = ''True'' Union All'
From	Information_Schema.Columns 
Where	Table_Name = 'SF_LearnDesignation'
		And Column_Name Like 'DoCourse%'

If Right(@SQL, 9) = 'Union All'
	Set @SQL = Left(@SQL, Len(@SQL)-9)

Exec(@SQL)

Replace the True in this line:

+ '] = ''True'' Union All'

with whatever value you store in the table to represent a true.

-George

Re: Table Colmn Names

07-15-2009, 11:23 AM

Contact

46 point Member
DerickC
Member since 08-14-2007, 12:39 AM
South Africa
Posts 90

Hi,

The following code works fine in Management Studio but when I run it from my ASP.net app I get the error below.

ERROR *****

Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid.
Invalid length parameter passed to the SUBSTRING function.

CODE ***

ALTER PROCEDURE [dbo].[elearning_course_table] -- 2526
	-- Add the parameters for the stored procedure here
		@ref varchar(10) 
AS
	 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare @SQL VarChar(Max)

begin try

Set @SQL = ''
Select @SQL = @SQL + ' (Select  ''' + module_lookup.modCode + ''' as ModuleCode, ''' 
+ module_lookup.ModuleName 
+ ''' From SF_LearnDesignation Where ID = ''' + @ref + ''' and  [' 
+ Column_Name 
+ '] = ''1'')'
+ ' Union All'
From INFORMATION_SCHEMA.COLUMNS INNER JOIN
                      module_lookup ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = module_lookup.ColumnName 
Where Table_Name = 'SF_LearnDesignation'
And Column_Name Like 'DoCourse%'
order by ordinal_position

If Right(@SQL, 9) = 'Union All'
Set @SQL = Left(@SQL, Len(@SQL)-9)
else
Set @SQL = Left(@SQL, Len(@SQL))

Execute(@SQL)
--print (@SQL)

end try
begin catch
print ERROR_MESSAGE()
end catch
end

Thanks for any help

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Page 1 of 1 (6 items)

RSS Available