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

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

T-SQL 2005 for same table join?

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
 I have below table with two columns.. Type      Code AB        Company_chris BC        Company_chis DE        Company_chis AB        Company_bob AB         Company_James BC        Company_James AB         Company_mark DE         Coampny_mark BC        Company_scott Unique value in TYPE column : AB , BC, DE Primary Key is :  TYPE and CODE I’m looking output in result query ......... Code                  Type1     Type2     Type3 Company_chris      AB         BC           DE Company_bob      AB         NULL       NULL Company_mark      AB         NULL       DE Company_scott      NULL      BC        NULL   Any t-sql 2005? Thanks.

View Complete Post

More Related Resource Links

sql server 2005 express - don't allow to create table

i have downloaded sql server 2005 Express version from the web and use domainname\SQLEXPRESS as server name but now i can't create table seems every create by sa user, what is the password of this user or how to grand right to my window login user so that i can modify/create something in database.

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

What is loweredrolename meant in aspnet_users table (SQL Server 2005)

For my existing data, both RoleName and LoweredRoleName are same, but some are lower case and some are in same case too. Anyone knows what is the use of column LoweredRoleName in aspnet_users table?For my existing data, both RoleName and LoweredRoleName are same, but some are lower case and some are in same case too. Anyone knows what is the use of column LoweredRoleName in aspnet_users table?

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

check if a table exists in SQL 2005

I'm trying to check if a certain table exists in a given database on a SQL 2005 Server. I've tried numerous times without any result.   can anyone point me in the right direction?

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

Join Filter for n to n Table

Hi all, We have a merge publication system in SQL 2008 R2. Publication is Enterprise Edition, Client's are Express Edition. Basically, we have 3 tables Roles, UserRoles,Users. Role : RoleID, RoleName columns User : UserID, UserName columns UserRole : UserID,RoleID columns   I want to filter these tables by HOST_NAME() function, which is equal to UserID for each subscription. IF i set Dynamic Filtering for User table (UserID=HOSTNAME()) , i receive only 1 row in User table in subscription database, ok. And also i set same Filter to UserRole table, and i can get only needed RoleID's to UserRole table, ok. But when i want to receive only necessary Roles to subscription database Role table, it does not work. I tried to use a Subquery, but i understood that it is a static filtering method. I tried Join filter for Role table, but i always receive all roles to Role table. I tried "SELECT <published_columns> FROM [dbo].[Role] INNER JOIN [dbo].[UserRole] ON [Role].[RoleID] = [UserRole].[RoleID]". But it receives all roles to Role table. I tried several Join filters for these tables but can't find a solution. How can i handle this?   Best regards.      

join - delete table(s)

I have two table:Parent has the key Id, UserIdChild has PaymentId, UserIdTo view the data using DropDownList.I want to delete information that is selected in the DropDownList (SelectedValue).My code:        products_to_delete var p = from and db.tbl_Payments                                 WHERE p.PaymentType.Contains (DropDownList1.SelectedValue)                                 Join in and db.tbl_PaymentDetails                                 He p.ID equals s.PaymentId                                 select p;        db.tbl_Payments.DeleteAllOnSubmit (products_to_delete);        db.SubmitChanges ();This code onl

3 table join how to question

I have an sql statement I want to convert to LINQ (VB.Net).  I cannot figure it out and was hoping for some help.SELECT COUNT(*) AS Expr1, SiteInfo.SiteName FROM Advice INNER JOIN InformixSource ON Advice.DSSERVER = InformixSource.PODServerName INNER JOIN SiteInfo ON InformixSource.SiteNum = SiteInfo.SiteNum GROUP BY SiteInfo.SiteName, SiteInfo.SiteNum ORDER BY SiteInfo.SiteNum  


help me plis. this thing drive me crazy. it really should be a simple solution but i cant find it, even with googling. i have subreport that display deposit transaction banking statement from account (customer). this subreport will be accessed through main report that loop all accounts in one branch, so its like a batch report processing for all customer in a branch. rendered to pdf and distributed to all our branchs. since i know that subreports header footer wont work on main report, i decide to move the subreport header into subreport table header and make it looks like it was a report header. but THERE is one problem i cant solve, there is ADDRESS BOX that should only VISIBLE on FIRST page for each customer, along with the other header that always visible. how im i supposed to do? please help. been searching n trying for 3 days and cant find any workin solution. been wondering do i really that dumm @_@ . thanks.   ps: sory for my bad english.  

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Joining Dimtime table by a referenced Join create a problem



I am trying to join a dimtime table by a refernced join to an orderlines table which is the fact.

(the referenced join was defined in the relationship matrix of the cube ).

The referenced table is the ordersheader which had no measures in it but only used a linking table between those two other.

The join from the dimtime to the ordersheader  is done by date and from ordersheader to orderlines by orderid

The problem is that in the cube browser measures are shown as they were multiplied across all dates (like the join was done in a wrong way )

I have created an sql query and the logic is working just fine when quering directly from the DB


What am I doing wrong

Join Tables For Definitions (Data Table/Definition Table)



I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

LINQ to Entity - using join for multiple table


I have my SQL query which needs to be conerted to Linq to Entity,

select pfr_sa.SID,pfr_sa.SourceGroupID,tx.txroll_cadaccountnumber,tx.txroll_StreetNumb

SSIS 2005 - Dynamically generate create table statement from source



I have a requirement where i had to migrate the table structure and data from ODBC database to SQL Server. I have dynamic source reader that uses sql query from variable to get the data and the next step is to get the create statement and create table and then to insert data. how can i get Create Statement the destination produces and use sql task to create the table and then insert the data using data flow task.

Please suggest



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