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
 General SQL Server Forums
 New to SQL Server Programming
 Column Names

Author  Topic 

Derick
Starting Member

17 Posts
Posted - 2009-07-07 : 05:39:05
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.

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!

NeilG
Aged Yak Warrior

530 Posts
Posted - 2009-07-07 : 05:54:35
How do you mean that the data is either true or false, do you mean there is a bit datatype column which is either a 1 or a 0 value. Which then indicates which student should do that course.

Otherwise can you supply the schema for the table by going to the specific table right click and select select table to.. and then for the next sub table create to.. next submenu Clipboard.. and then paste that on here.

This will give us all a better look at the problem.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2009-07-07 : 05:59:40
quote:
I need to list the column names

You only want the lists of columns name of that table is it ?

Get from INFORMATION_SCHEMA.COLUMNS


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Derick
Starting Member

17 Posts
Posted - 2009-07-07 : 06:03:33
Thanks for the reply see below, and fist row data below that

USE [Protweb]
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


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!
Go to Top of Page

Derick
Starting Member

17 Posts
Posted - 2009-07-07 : 06:06:44
I need somthing like below but only for the columns with a value on 1

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

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!
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-07-07 : 11:46:26
What version of SQL are you using. If 2005 or greater look into the PIVOT operator.
Go to Top of Page

Derick
Starting Member

17 Posts
Posted - 2009-07-07 : 11:51:11
I'm looking at using somthing like the code below. I do not know how to get it into a temp table. I need to link the col names to the course names.

Declare @SQL VarChar(Max)

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

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

Exec(@SQL)


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!
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2009-07-07 : 11:56:02
if you're looking to select just those columns which have 1 in them, do a UNPIVOT to get column names and their values as two columns of a unpivot table, then select the ones which have 1, and create a dynamic query with these selected column names.

unpivot example

SELECT coursename, 
       field_value 
FROM   (SELECT [DoCourse1Mod1], 
               [DoCourse1Mod2], 
               [DoCourse1Mod3], 
               [DoCourse1Mod4], 
               [DoCourse1Mod5], 
               [DoCourse1Mod6], 
               [DoCourse1Mod7], 
               [DoCourse1Mod8], 
               [DoCourse1Mod9], 
               [DoCourse1Mod10] 
        FROM   sf_learndesignation 
        WHERE  id = 1) t 
       UNPIVOT 
       (field_value 
        FOR coursename IN ( [DoCourse1Mod1],[DoCourse1Mod2],[DoCourse1Mod3],[DoCourse1Mod4],[DoCourse1Mod5],[DoCourse1Mod6],[DoCourse1Mod7],[DoCourse1Mod8],[DoCourse1Mod9],[DoCourse1Mod10] ) ) AS unpvt
Go to Top of Page

Derick
Starting Member

17 Posts
Posted - 2009-07-07 : 12:24:54
Thanks rohitkumar,
Im reading the data from a table not managed by me, the colums may change at any time and I will not be warned. I need it to read all columns with out me hard coding it.


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!
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2009-07-07 : 13:35:45
then even this query will have to be dynamic...like you're getting columns from information_schema.columns
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site