I have a row that looks like this
Year 2006 2007 2008 2009.....etc.
I want a select statement that will get me this
Year
2006
2007
2008
2009
etc.
Pivot fields into a single column
Transact-SQL
-
- Previous
- 1
- EncryptSensitiveWithUserKey - question Yes, it would seem simple. But I don't have any other logins to the server but my own, as I am a consultant, and asking for someone else's login info for testing purposes is beyond the scope of my authority, yet I have to take other people's logins into account. So, it would seem that since I'm not trying to protect anything in the package including passwords, I might as well set it to "DontSaveSensitive", yes Tag: Pivot fields into a single column
- 2
- @@FETCH_STATUS Reset Thanks very much. I need to calculate the number of business days between two status events. Each is recorded in a row in the table, eg, Order Event1 Date 1 Open June 1 1 Hold June 10 1Close June 30 2 Open July 1 2 hold July 10 I plan to fetch a row, save the values, fetch another row and calculate the days, continuining unitl a change in order and write output accordingly. If you can suggest a set based approach I am all for it, however can anyone tell me why the @@fetch_status is not being reset. Tag: Pivot fields into a single column
- 3
- Object reference not set to instance of the object while trying to sync. I tried the same steps on another domain and it worked. Thanks. Tag: Pivot fields into a single column
- 4
- OLE-Db connection in Transformation Script Component Thank you. That was the nudge in the right direction that I needed. Greg. Tag: Pivot fields into a single column
- 5
- Installing Client Tools That's very helpful - thank you. It would also be nice to know what the licensing rules actually are & what the best practice is supposed to be. Tag: Pivot fields into a single column
- 6
- Comparing Large Tables Well, when you're doing a lookup for tens of millions of records against another table with tens of millions records I would think it becomes pretty considerable since you're doing a row-by-row comparison. Do you think it would be faster to stage the tables and try comparing them through T-SQL or just use SSIS's lookup component Tag: Pivot fields into a single column
- 7
- How to delete rows in tables... sdj_dk wrote: I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database. Im using SQL 2000 I have tried with the following: Delete from fsalesinvoiceline Join dsalesinvoiceheader on Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and Fsalesinvoiceline.company= dsalesinvoiceheader.company Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯ Go Delete from dsalesinvoiceheader Where dsalesinvoiceheader.billtocustomerno=¡¯INDTAST DEBITORNUMMER¡¯ I get the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN' What am I doing wrong /Soren D. Jensen BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available. DELETE FROM fsalesinvoiceline, dsalesinvoiceheader WHERE fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER' Tag: Pivot fields into a single column
- 8
- Maximum height for table Hi Greg, Do you know how does this height create any difference, what i mean is usually this doesnt restrict the table from expanding or so. Tag: Pivot fields into a single column
- 9
- any *simple* external activation examples available? BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.) TIA Tag: Pivot fields into a single column
- 10
- My SSIS problems TheViewMaster wrote: By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID I disagree. What should the default be, then Tag: Pivot fields into a single column
- 11
- Filtering Problems I am stumped on what is probably a simple glitch. I have an SSRS 2005 server report based on a stored procedure dataset, which accepts a single parameter. The parameter value originates from a session variable when the user opens the aspx page containing the reportviewer control (Visual Web Developer 2005). I've configured this parameter as a hidden parameter in the RDL design. In this configuration it functions as intended in both the development environment and when deployed. Next, I tried to add user prompts to implement report server filtering (in addition to the database filtering in the source SPROC). I added the parameters, set up the data sources for the prompt values, set up the filter expressions, did everything that I can find to implement this process. When I preview the report in the development environment, it works as intended. When I deploy it and attempt to run it from my http://localhost environment, though, it fails. There's no error message or anything...I don't even get the "Report Running" icon with no rows returned...what I get is prompts which function correctly (showing the right values and letting me select one), but when I click "Run Report" I just get an immediate screen refresh with the prompts reset to the default "<Select a value>" setting. And no report. I've obviously missed some setting somewhere, for it to work in dev but not when deployed. Any suggestions will be GREATLY appreciated. Tag: Pivot fields into a single column
- 12
- Data from Derived Table I am looking to run two different queries. the first one returns about 6000 numbers. i.e 123454, 15432, 2343545 etc My second query i want to use the results from the first query as a where clause i.e where Number in ( '123454' , '15432' , '2343545' ) Clearly i dont want to type out 6000 numbers in a query. How do i use the data generated in the first query as the where criterea in the second query. Tag: Pivot fields into a single column
- 13
- Cannot load Data Flow tasks Hi CodeDigger, A Question for you is: If you a create a new package on your desktop and drag a dataflow on the Controlflow, does that work fine If your answer is no, then it seems that it's clearly some other issue not related to deployment.Trying the following steps might help. First try the Step 3, if it doesn't work, try Step 2 followed by step 3, Finally make sure that Step 1 is done before you use this in your desktop. While deploying to other machine, make sure you do not use EncryptSensitivewithUsekey, Instead use EncryptSensistive with Password. and provide a password. Go Add / Remove Programs, Click Change/Remove, Repair the Installation and Apply SP2. In the run command, type devenv /setup. Now open the BIDS, Check if you can add the dataflow task into the control flow. Thanks Subhash Subramanyam Tag: Pivot fields into a single column
- 14
- SQL 2005, OPENQUERY and security context switching Hi, I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine: EXECUTE AS LOGIN = 'link_user' SELECT * FROM OPENQUERY ( linked_server , 'SELECT * FROM ... ) But when I use the exact same statements in a stored procedure I get the folllowing error: Msg 7416, Level 16, State 1, Procedure linktest2, Line 5 Access to the remote server is denied because no login-mapping exists. I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures! Is there another workaround then to create all users in the linked server security Rgds Bertrand Tag: Pivot fields into a single column
- 15
- Linking Oracle view to SQL Server express I have used MSDAORA. How do I use OpenQuery. Can you please provide an example Thanks, Stara Tag: Pivot fields into a single column
- 16
- Query problem involving DateTime column It shouldn't return any results. It will only return null values when there are null values in the table(s) Adam Tag: Pivot fields into a single column
- 1
- Next
- 1
- Access project connecting to SQL 2005 Thanks for the reply but we have done what you are suggesting and it works great when you access through Management Studio. Our users are using MS Access projects. Maybe I am missing a step but if we don't give db_datareader they won't even see these views and when they do have db_datareader all objects that do not have the "dbo" schema doesn't seem to work. It will show another schema entirely. It looks like a login name and not necessarily yours. We're talking about installing Access 2007 but I don't know when. Maybe it will work better. I did test the beta version and it appears to work but the new Access environment is so much different and only being a casual user of Access I don't know if there are any other problems. Tag: Pivot fields into a single column
- 2
- Filters - Report Builder A model has been exposed for the business users and they are trying to fiter the data in the report builder. So there is no sql query involved at the report builder level in order to use your suggestion. any other ideas Tag: Pivot fields into a single column
- 3
- Connection Managers Area not coming up Phil, Yes, I do have Integration Services project opened. In fact I was following the instructions to get a hang by doing the SSIS Tutorial. BTW thanks for the promp attention. Regards Mamidi Tag: Pivot fields into a single column
- 4
- How to make this query... Hi ggciubuc This wont do it right.. This query will delete the whole table. Code Snippet select Symbolid from db_stockprices where Date<>@yourdatavalue this will select all symbolIDs that does not have @yourdatevalue which is most cases is the whole SynbolIDs.. Am i correct Tag: Pivot fields into a single column
- 5
- What is the best book to start from scratch? Hi Kent - Thank you for replying. That's a start. I keep accumulating more resources, but keep making little progress using SQL Server. So now I have Books Online installed. I went through and did not find an online book for what to do after installing SQL Server. Don't I need to create a database Is there a resource for starting from there Not trying to be thick, I just am Thanks. Doug Tag: Pivot fields into a single column
- 6
- Mirroring on separete domains We have 2 servers each having a domain (i.e. 2 domains) running Windows Server 2003 Standard Edition Service Pack 1 We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error The error produced from the mirroring part is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error : 1418) Thank you. Tag: Pivot fields into a single column
- 7
- Trigger with access to remote server Can u show me good base of knowledge about this problem I need know what happend when connection crashed, if server try make triger again after new connection, etc. Best regards Jakub Kuzela. Tag: Pivot fields into a single column
- 8
- problems at beginning of set up I'm afraid this didn't work. The service isn't listed. There is nothing with a NS$ prefix. Tag: Pivot fields into a single column
- 9
- Trigger? basically, this problem comes when you pass the parameter as char(0) or ' ' to the sp or adhoc query. This always happens due to ignorance. CREATE TABLE dbo . t2 ( col1 int , col2 datetime ); INSERT INTO dbo . t2 SELECT 1 , null INSERT INTO dbo . t2 SELECT 2 , getdate () INSERT INTO dbo . t2 SELECT 6 , '' -- This statement will insert the datetime as 1900-01-01 00:00:00.000 because you have supplied char (0) string GO select * from t2 so the best way to restrict is to create a Check constraint and also supply null if there is no value in the frontend text box. I don't think you need to write trigger to do this. If you want to write it in trigger use Case statement to check the value (which i will never suggest) Madhu Tag: Pivot fields into a single column
- 10
- Report connection fails with DNS name After doing some investigation, I have found that the issue is with Analysis server not able to connect using the DNS name (Note: my Reporting Service proj is connecting to Analysis services datasource). When I connect to Analysis server through SQL Server management studio (by def which uses Win authentication), and specify machine name (ABC) in Server then it connects but if I give DNS name (XYZ.PWR.COM) it gives me the following error: ************************************************************************************************************ A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient) ------------------------------ Program Location: at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() =================================== Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System) ------------------------------ Program Location: at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream) at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord() at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType() ************************************************************************************************************ If I connect to Reporting Service using DNS name then I get the following error: ************************************************************************************************************ =================================== Cannot connect to XYZ.PWR.COM. =================================== A connection to the report server failed. Most likely, you do not have permission to connect to the computer. You should either try to connect using a report server URL for the Server name, or if the Distributed COM Users group exists on the remote computer, verify that you are a member. (Microsoft.SqlServer.Management.UI.RSClient) ------------------------------ Program Location: at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName) at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci) at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci) at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() ************************************************************************************************************ When I ping with machine name (ABC) and DNS name (XYZ.PWR.COM), both resolves to the same IP. C:\Documents and Settings\Administrator>ping ABC Pinging ABC.corp.abctech.com [xx.x.xx.xx] with 32 bytes of d C:\Documents and Settings\Administrator>ping XYZ.PWR.COM Pinging XYZ.PWR.COM [xx.x.xx.xx] with 32 bytes of data: Is this problem because of the kerberos authentication that Analysis server uses to authenticate I am able to connect to the Database engine using DNS name (but using SQL Server authentication). Also I was able to view the reportserver and reports page by changing the IIS authentication to NTLM. But finally I am not able to view the reports. Can anyone please help me in solving this issue. Awatiitng some positive reply. Tag: Pivot fields into a single column
- 11
- Creating Stored Procedure in SQLServer2005 I think that this will be a matter of becoming more familiar with the interface. If you are well aquainted with creating stored procedures in SQL 2000 then you will adjust to the 2005 interface rather quickly. Are you perhaps being confused by the template that you can get when you create a new procedure Tag: Pivot fields into a single column
- 12
- Secure a single SQL Server database + Make float fields encrypted So what will be the right path to follow to create logins, database permissions, and roles I should say that the server is not using the windows authentication mode. For now, I would be happy if I could block the other companies from accessing the database. When you say list the tables you mean just the table names Or the design of the tables Thanks! Tag: Pivot fields into a single column
- 13
- Stored procedure assembly Error Hi, thanks for the response, I cant reference the following; Imports Microsoft.SqlServer.Server Where can i find this to use the windows SQL authentication Tag: Pivot fields into a single column
- 14
- Referencing a conditional column in the WHERE clause -- Possible? I agree with Arnie for the most part. This will work: SELECT S.StationID , P.PoleID --NOTE: change to this means a change to the where clause for Actual_latitude!! , CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude ELSE NULL END AS Actual_Latitude , CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude ELSE NULL END AS Actual_Longitude FROM Stations S LEFT JOIN Poles P on S.PoleID = P.PoleID where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude ELSE NULL END > 50 --Actual_Latitude > 50 In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think): WITH stationQuery AS ( SELECT S.StationID , P.PoleID , CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude ELSE NULL END AS Actual_Latitude , CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude ELSE NULL END AS Actual_Longitude FROM Stations S LEFT JOIN Poles P on S.PoleID = P.PoleID select * from stationQuery where actual_latitude > 50 The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000. Tag: Pivot fields into a single column
- 15
- Returning resulsets between procedures !! Hello, could you please provide me an Example or tell me how to do it Tag: Pivot fields into a single column
- 16
- Communication between tasks in an SSIS Package Hi, I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file any input is appreciated. Fahad Tag: Pivot fields into a single column
- 1
- Previous
Re: Pivot fields into a single column
hunchback
Try:
Code Snippet
create
table #t (c1
int not null,c2
int not null,c3
int not null,c4
int not null)
insert
into #t values(2006, 2007, 2008, 2009)
select
case t2.c1 when 1 then t1.c1 when 2 then t1.c2 when 3 then t1.c3 when 4 then t1.c4 end as [Year]from
#t
as t1 cross join (select 1 as [c1] union all select 2 union all select 3 union all select 4) as t2
-- 2005
select
[Year]
from
(select c1, c2, c3, c4 from #t) as p unpivot ([Year] for [c] in (c1, c2, c3, c4)) as unpvt
drop
table #t
AMB
Re: Pivot fields into a single column
WVUProgramer
Thanks, but I can't use create table or insert. Appreciate the effort though.
Thanks, but I can't use create table or insert. Appreciate the effort though.
Re: Pivot fields into a single column
Sam Vella
You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.
You just need to use the select part of it (possibly modified to meet your exact requirements)
WVUProgramer wrote:
Thanks, but I can't use create table or insert. Appreciate the effort though.
You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.
You just need to use the select part of it (possibly modified to meet your exact requirements)
Re: Pivot fields into a single column
WVUProgramer
For the first select, it says there's no from clause in the select statement following the cross join.
In the second one, it says unable to parse text.
I plugged in my field names (which are f1 through f4) and my table name where the t# is
Perhaps i'm confused about the 
and the [Year].
Re: Pivot fields into a single column
hunchback
If you copy and paste the script, then execute it, you will see the result is the same you are expecting in your original post.
Can you post the statement you are trying to execute
AMB
Re: Pivot fields into a single column
WVUProgramer
select
case t2.F1
when 1 then t1.F1
when 2 then t1.F2
when 3 then t1.F3
when 4 then t1.F4
end as [Year]
from
table1 as t1
cross join
(select 1 as [F1] union all select 2 union all select 3 union all select 4) as t2
My table name is table1 my fields are F1, F2, F3, F4
Error Message: Error in SELECT clause: expression near 'UNION'.
Missing FROM clause.
Unable to parse query text.
Re: Pivot fields into a single column
hunchback
Where are you testing the script: (which application)
AMB