[T-SQL] select rows into colums |
| Posted: 27 Feb 2013 10:19 PM PST Dear T-sqlérs,I have a test table (see script below) with the following resultname lengthbalk1 7balk1 6balk1 9stof1 6stof2 6stof3 6stof4 6stof5 6stof5 7stof6 6stof7 6stof8 6stof9 6stof9 7stof10 6stof11 6stof12 6Now I would like the result to be like this:balk1 stof1 stof2 stof3 stof4 stof5 stof 6 stof 7 stof 8 stof 9 stof10 stof 11 stof 126 6 6 6 6 6 6 6 6 6 6 6 67 7 7 9 How should my query be?Thnx a lot in advance for your help :)[code]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[test]( [name] [varchar](50) NULL, [length] [int] NULL) ON [PRIMARY]GOinsert into test (name, length) values ('balk1', 7)insert into test (name, length) values ('balk1', 6)insert into test (name, length) values ('balk1', 9)insert into test (name, length) values ('stof1', 6)insert into test (name, length) values ('stof2', 6)insert into test (name, length) values ('stof3', 6)insert into test (name, length) values ('stof4', 6)insert into test (name, length) values ('stof5', 6)insert into test (name, length) values ('stof5', 7)insert into test (name, length) values ('stof6', 6)insert into test (name, length) values ('stof7', 6)insert into test (name, length) values ('stof8', 6)insert into test (name, length) values ('stof9', 6)insert into test (name, length) values ('stof9', 7)insert into test (name, length) values ('stof10', 6)insert into test (name, length) values ('stof11', 6)insert into test (name, length) values ('stof12', 6)select * from testSET ANSI_PADDING OFFGO[/code] |
| Create SP on Multi-Server Query Posted: 13 Mar 2013 11:40 PM PDT I have a dilemna. I like to use multi-server quesier to push out my code; however, all my SP's use DMV's or sys.ViewName. These are obviously incompatible with Earlier legacy systems of SQL Server. So what I would like to do, is check for the version number, if it equal or less than 8, return out of the SP Create command.So I initially wrote a statement and placed it at line one of the SP:if left(cast(ServerProperty('ProductVersion') as varchar), 1) = '8' returngoThe problem is the go statement, it processes the return and go statements then just moves on the the next line of code, which is to check for the existance of a DB in sys.databases (obviously breaks for SQL 7/2000 as no such table or view existed).So my short question; is there a way to prevent, during a multi-server query/execution, the execution of a Create SP if the version of the server is 7/2000? |
| Posted: 13 Mar 2013 07:49 AM PDT I'm trying to chekck the existance of a table first and then get a value ONLY IF when the table is there. So, I tried this;if exists (select name from [LinkedServer].[dbName].[sys].[sysobjects] where name = 'tableName1')begin insert into tableName2 (value1, values) select values1, value2 from [LinkedServer].[dbName].dbo.tableName1endHowever, this statement still executes even if "IF EXITST"returns nothing and fails with "table does not exists".Can someone tell me what am i doing wrong here?Thanks!! |
| Posted: 13 Mar 2013 07:57 AM PDT If I want to Sum a field where the date has passed, how would I do that? So my goal would be to Sum the OpenAmountUSD where the dueDate has gone by as a new column called OpenPastDue[code="sql"]SELECT vfact_aptransaction_datepart.openamountusd AS OpenAmtUSD, dim_site.siteid, dim_site.sitename, dim_site.division, vfact_aptransaction_datepart.duedate, dim_vendor.vendorname, dim_vendor.vendorclass, vfact_aptransaction_datepart.weekdue, vfact_aptransaction_datepart.yeardue, dim_site.sitedescriptionFROM vfact_aptransaction_datepart INNER JOIN dim_site ON vfact_aptransaction_datepart.siteid = dim_site.siteid INNER JOIN dim_vendor ON vfact_aptransaction_datepart.vendorid = dim_vendor.vendorid [/code] |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment