Monday, March 4, 2013

[SQL Server] How to split query result into seperate columns

[SQL Server] How to split query result into seperate columns

How to split query result into seperate columns

Posted: 04 Mar 2013 12:45 AM PST

I have one table that contains all my data. I can group into a result as below (the weight would the the sum per day):[u][b]Time[/b][/u]_____________________[u][b]Plant[/b][/u] ______[u][b]Weight[/b][/u]01/03/2013 10:00___________AP1________1.202/03/2013 11:00___________AP1________2.303/03/2013 12:00 __________AP1 ________1.101/03/2013 10:00 __________AP2_________1.402/03/2013 10:00 __________AP2 ________1.103/03/2013 10:00 __________AP2_________1.1How do I go about splitting the sum(weight) up into seperate columns based on 'Plant' so the query result looks like this:[b]Time[u][/u][/b]_____________________[b]AP1[u][/u][/b]______[b]AP2 [u][/u][/b] 01/03/2013 10:00__________1.2_______1.4 02/03/2013 11:00__________2.3_______1.103/03/2013 12:00__________1.1_______1.1Basically I want to group the results into columns rather than one long result.Thanks

UNION statement and duplicate rows issue

Posted: 04 Mar 2013 05:09 AM PST

I'm guessing I'm misunderstanding how to use UNION or its results. But here's what I have and the results:SELECT a.PLANT_NO AS PlantNumber ,SUM(a.APPLIED_VOL) AS AppliedVolume FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_SETTLE_FEE] aGROUP BY a.PLANT_NOUNIONSELECT CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END AS ResolvedPlantNumber ,SUM(TRANS_VOL) AS AppliedVolume FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_RPTS_INVOICE_DTL] aLEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] e ON a.ctr_no = e.[ContractNumber] AND a.PROD_DT BETWEEN e.[PlantEffectiveFromDate] AND e.[PlantEffectiveToDate] AND a.PROD_DT BETWEEN e.[ContractEffectiveFromDate] AND e.[ContractEffectiveToDate] AND a.plant_no = 'ALL'GROUP BY CASE WHEN a.plant_no = 'ALL' THEN e.[PlantNumber] ELSE a.plant_no END Example result:PlantNumber --- AppliedVolume002 --- 324334.00 002 --- 215943448.81043 --- 3513440.00043 --- 407522014.65Is there a way for me to have the plant numbers grouped together so there aren't duplicates?Such as:PlantNumber --- AppliedVolume002 ---- 216,267,782.81 043 --- 411,035,454.65

Problem with a query - wrong update results

Posted: 04 Mar 2013 05:34 AM PST

Hello experts,I have 2 tables.One of the tables has the data I need to know which user input it. On the table I have a column name called owner_m, when I run the data import for some reason my sistem is bringing me instead of the user_names the id_user_numbers that is store in tbluser table.I need to update my data table with the users name and I have the following query for that:update Aset final_owner=tblUser.tNameFROM A INNER JOINtblUser ON cast(A._owner_m as int) = cast(tblUser.aUserID as int)owner_m is a varchar and User id is an int. Thats why Im using the cast option.But for some reason the query is giving me the same user id for all the fields as follows:owner_m tname19 Carol20 Carol28 Carol instead of19 Carol 20 Rolando28 Estefania.Im asumming is because the data type and I already fix that with the cast function.I dont know what else to do, please help!!!

Linked Server Anonymose Logon Error

Posted: 04 Mar 2013 04:54 AM PST

Hi,So we are changing the SQL service account. The account we are changing to is in a domain user and the previous one was a domain admin. The account is inside a group and the group has been added to the local administrators group per server using a group policy. The group has been given logon rights to the servers, and the sysadmin server role.When I changed the sql service account to the new account it seemed to go fine. A few days later developers are complaining about linked servers being disconnected. So I logged in to a server through ssms that had a link server to the server that I changed the account on. When I test the connection to the linked server that had the service account changed I get "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)."Are there specific permissions that a domain account needs to be used for link servers? We temporary resolved the issue by changing the logon under the linked server properties to the sa account, but the sql service still runs as the newly changed account. I have seen some documentation but it's pretty hard to follow i'm pretty new to the more complex areas of sql.

Sql Server 2005 doesn't connect for windows xp SP3

Posted: 17 Dec 2012 12:53 PM PST

I have installed sql server 2005 in Windows XP SP3. Installed successfully but [b]a) server didn't connect gave error message ;[/b]TITLE: Connect to Server------------------------------Cannot connect to localhost.------------------------------ADDITIONAL INFORMATION:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)again tried with computer name gave same message.b) Service is not listed in the windows service list (should have service name mssqlserver)c) Version information Sql Server 2005 :Microsoft SQL Server Management Studio 9.00.1399.00Microsoft Analysis Services Client Tools 2005.090.1399.00Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)Microsoft MSXML 2.6 3.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.5512Microsoft .NET Framework 2.0.50727.42Operating System 5.1.2600Can anyone help me to run sql Server 2005 in windows xp sp3?

Case Statement - DateDiff and DatePart

Posted: 03 Mar 2013 06:14 PM PST

I have been asked to come up with a case statement that gives me the date of birth, no I know there are easier ways to do it but its about writing the actual statement which I have done but I have got a bit confused.Can you Help?Declare @DOB DatetimeDeclare @Today DatetimeSet @DOB = '01 November 1971'Set @Today = GETDATE()SELECT Datediff(Year,@DOB,@Today) AS Years, Datepart(Month,@DOB) As DOB_Month, Datepart(Day, @DOB) as DOB_Day,DatePart(Month, @Today) As Current_Month, Datepart(Day,@Today) AS Current_DayCASE WHEN DatePart(Month, @Today) < Datepart(Month,@DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSEDatePart(Month, @Today) = Datepart(Month,@DOB) THEN Datepart(Day,@Today) < Datepart(Day, @DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSE END
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog