.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to write a SQL query to present values horizontally sql server 2005

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
Suppose I have one table called Jobs:

    [JOBID] [int] IDENTITY(1,1) NOT NULL,
    [PARTDESC] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OrderPlacedBy] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [SpecialistName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Priority] [int] NOT NULL,
    [Symptoms] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CustomerNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ShopNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobType] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JobState] [nvarchar](32) COLLATE Latin1_General_CI_AS NULL,
    [JobAddedDate] [datetime] NOT NULL,
    [JobStartedDate] [datetime] NULL,
    [JobFinishedDate] [datetime] NULL,
    [JobShippedDate] [datetime] NULL,
    [RecievedDate] [datetime] NULL

I want to see the specialist name and his jobs IDs horizontally.

ANA 201,502,605,701,774
BEN 102,103,051
JEN 705,401,402,509,409,408

A s

View Complete Post

More Related Resource Links

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

How to query the Active directory using SQL Server 2005

Hi, I am trying to query Active server from Sql Server 2005 and getting error I used following script to create link server EXEC sp_addlinkedserver 'ADSI3', 'Active Directory Services 111', 'ADSDSOObject', 'adsdatasource' and then trying to connect using following statement SELECT   * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=local.mycompany.ca'' WHERE objectCategory=''User'' ') and getting following error An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=local.resound.ca' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Please let me know if you know How to query the Active directory using SQL Server 2005 Thanks, Raj    Dreaming a world without any war in anywhere

Runtime filter values differ from values setup in designer. SQL server 2005



I am confused as i have setup values for the filter criteria for company as 'JTWO' & 'STOC' in design mode of report builder however when i access the report from report manager the values that prompted  @ runtime are AARFT05F and ARRTUEID'.

any reason why report builder should change the filter criteria @ runtime.








Default values for filters not populating in report builder (SQL server 2005)



I  am frustrated with this challenge for  populating default values for filters in report builder. Here are the steps that I am performing.

I have clicked on the filter icon, then dragged the dimension to the filter pane, switched the properties to Prompt and changed the operator to 'in this list'.

 by default the value selected is 'no value selected', Microsoft online tutorials says that this should automatically populate the filter criteria with the default list of values for the underlying dimension. However it dosen't

How do I populate this filter with default list of values.

Please help.










How to write a trigger when given a condition in SQL SERVER 2005?


Hi Guys,
I am using SQL SERVER 2005.

I know how to write a trigger for a basic insert or update or delete. But here in my case I have to use the below condition and create a trigger.

I havent used any trigger till now in my job. So I am facing a slight problem in understanding and writing the code.....
I got the following requirement to create a new trigger for the following scenario :

The user (john) should be notified when the following happens :

Any transaction entered to account 100 which is not a ABN,CCH,or TTW
Any transaction entered to account 189 which is not a KHD

Description of fields :
100 and 189 are values for AccountCode Column in Account Table
ABN,CCH,TTW and KHD are values for CompanyTypeCode Column in Company Table.
I need to create new trigger on DLT_Data table

getting error while inserting values into table in sql server 2005

i have connected to sql server n my connection string is

conn.ConnectionString =

"data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\subi.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

But i am not able to insert values into the table..

am getting sqlexception error.... and msg as invalid object name "staff" as staff is my table name.. before this i had some problem with connection then i cleared that problem then after that this problem occured.. plz guide me on this ... i need to complete my project as soon as possible

Tracing of query in sql server 2005

hi friends,

Is it possible that while a query is running in sql server 2005 but cann't be possible to trace it in profiler or in activity monitor.

Thanks in Advance

slective order by in sql server 2005 query

except ascending or decending can we define our own choice of order by, like if i set order by hour it display 0(zero) first, but if time start form 1800 then how to display 18 first then 19 then 20 then so on to 5am can any body help in sql 2005 server

How to write storeprocedure update special character in Sql server 2005


Hi All,

I am using Sql Server 2005,My table structure is below.

Create Table ComentTable(id int,coments varchar(4000))

insert into ComentTable values(1,'sanjay I don%27t Know Tamil Language')

insert into ComentTable values(1,'sanjay I don%27t Know Kanar Language')

how to write storeprocedure update coments column replace %27 to '

select query sql server 2005


i have three table

1)    tbl_pages              primary key column is  (intPk_PageID)

2)   tbl_pagecontents   foreign key colum (intPk_PageContentID) reference with tbl_pages

3)   tbl_banners           foreign key colum  (intFk_pageid) reference with tbl_pages

select * from tbl_pages
intPk_PageID   vcr_PageTitle    vcr_PageUrl         int_PageStatus
1                      Contact Us          contactus.aspx             1
2                      News                   news.aspx       

how to store query string in sql server 2005


Hi all,


how to store query string in sql server 20005 

[Sql Server 2005] Need help to query with joins


Hi all :)

I hope anyone save me from madness ...

here is my query

                      t1.date, t1.employeeID, t2.name AS customer,

                     t3.description AS service, t4.total as amount

FROM            Trg_EmployeeCalendar AS t1 LEFT OUTER JOIN
                    EmployeeJobs AS t4 ON t1.employeeID = t4.employeeID AND t1.date = t4.date LEFT OUTER JOIN
                    Customers AS t2 ON t2.id_location = t4.locationID LEFT OUTER JOIN
                    ServiceCategory AS t3 ON t3.id_service = t4.serviceID LEFT OUTER JOIN

With this query, if an employee has two jobs in different l

Executing XML query from SQL 2005 pointing to SQL 2000 server

Occurs with root command.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= ADODB.Command  Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.    Error on Line 18.  The step failed.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= Microsoft OLE DB Provider for SQL Server  Error Description: Line 1: Incorrect syntax near 'ROOT'.    Error on Line 20.  The step failed.
Dim oCmd, sSQL, oDom
   Set oDom = CreateObject("Microsoft.XMLDOM")
   Set oCmd = CreateObject("ADODB.Command")
  oCmd.ActiveConnection ="Driver={SQL Native Client};Database=Validation;Server=server1;Uid=sa;Pwd=;"
   sSQL = "<?xml version=""1.0"" ?>"
   sSQL = sSQL &

How to save checkbox values into sql server 2005 via c#

Hi there

I want to know how I can save a checkbox value into sql server 2005?  The check box is in my windows forms application...


Help converting an Acesss SQL query to SQL Server 2005 query


I am trying to convert this microsoft access query to sql 2005 query. Thank you for taking the time to look.

(This is taking a markup field (MultiplierCode) with a value like 1.45/1.5 and splitting into 2 numbers (1.45 would be the regular markup (based on the pay rate) and the 1.5 would be the overtime markup of the Bill Rate. Then updating orders and assignments based on that.)

UPDATE Order_Root INNER JOIN assignmentroot ON Order_Root.OrderID = assignmentroot.OrderID SET Order_Root.Bill = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), Order_Root.OBillRate = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1))*(Right([order_root]![MultiplierCode],Len([order_root]![MultiplierCode])-InStr(1,[order_root]![MultiplierCode],"/"))), Order_Root.DBillRate = [pay]*(Left([order_root]![MultiplierCode],(Len(Left("order_root]![[MultiplierCode]",InStr([order_root]![MultiplierCode],"/"))))-1)*2), assignmentroot.BillRate = [payrate]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), assignmentroot.OBillRate = [payrate]*(Left([order_root]![MultiplierCo

SQL Server 2005/2008 Query Monitoring via DMVs


My understanding is that sys.dm_exec_query_stats is for archive aggreated query statistics so I’ve been using the query below to monitor active long running queries and it works fine for single statements – however it seems to return hugely inflated total_elapsed_time values with some procs or functions where multiple statements are run. I’m also interested if anyone know how to find query completion times from DMVs?


Select …<some columns>
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st
INNER JOIN sys.dm_exec_sessions AS es
ON es.session_id = er.session_id
INNER JOIN sys.dm_exec_connections AS ec
ON ec.session_id = es.session_id
where er.total_elapsed_time / 1000.0 > @ElapsedThreshold
ORDER BY er.total_elapsed_time DESC;

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend