SQL Server 2008 :: Joining Two Columns To Same Table?

Aug 17, 2015

I have two tables: "Users" and "Messages".

The Messages table has a "UserFromID" and an "UserToID", indicating a message was sent from a user to a user.

The Users table has a UserID, FirstName, LastName and UserGroup.

Im trying to get all messages sent by or to a user in a specific group, with the First and Last names of the sending and receiving users.

Here is my tables and data:

CREATE TABLE tmp_users (userID INT, GroupID INT, UserName VARCHAR(10), FullName VARCHAR(100))
CREATE TABLE tmp_Messages (aKey INT IDENTITY, FromUserID INT, ToUserID INT, msg VARCHAR(100))
INSERT INTO tmp_users VALUES (1,1,'John','John Smith')
INSERT INTO tmp_users VALUES (2,1,'Jane','Jane Doh')
INSERT INTO tmp_users VALUES (3,1,'Bob','Bob Newheart')


The query Im running so far is wrong, but here it is...

SELECT t.FromUserID, t.ToUserID, t.msg, u.UserName AS UserFrom,
u.GroupID AS FromGroup, u2.UserName AS UserTo, u2.GroupID AS ToGroup
FROM tmp_Messages t
LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u

[Code] .....

im missing the details of one of the users.I know what the problem is, I just cant figure out how to get this working without using temp tables, which I cant do in the production version.

View 3 Replies


SQL Server 2012 :: Joining Dim To Fact Table Where Dim Table Key Exists In Multiple Fact Columns

Oct 26, 2015

Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?

IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;

[Code] ....

I'm using very small data at the moment, and the query plan and statistics don't really say which way.

View 2 Replies View Related

Joining Columns Within Same Table?

Sep 20, 2013

What sort of script would convert a pre-existing table into the second below?

I only want to merge the columns with Primary_IDs 1111 & 3333 to have the same Secondary_ID values, without duplicating any similar Secondary_ID values between the 2 which I've marked in red below.

Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
3333 | 1
3333 | 2
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78


Primary_ID | Secondary_ID
1111 | 1
1111 | 2
1111 | 3
1111 | 4
1111 | 10
1111 | 20
1111 | 100
1111 | 200
3333 | 1
3333 | 2
3333 | 3
3333 | 4
3333 | 10
3333 | 20
3333 | 100
3333 | 200
5555 | 12
5555 | 34
7777 | 56
7777 | 78

View 11 Replies View Related

Joining Several Columns From Same Table

Oct 15, 2006


Sorry if this has been asked before, I looked through the FAQ but could not find an answer to the following.

I have a Project table which contains amongst other fields, a CreatedByID field and a LastModifiedByID field. Both these fields point to a User table. What I would like to do is get the two usernames from a query on the project table.

I know how to get one usename using the following Sql command but how do I get access to the second username ?

SELECT Project.Name,User.Username FROM Project,User WHERE Project.ID=@id AND User.ID = Project.CreatedByID;

I hope my ramblings make sense



View 2 Replies View Related

SQL Server 2008 :: Return A Single Table With Three Columns

Jan 26, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 2015/01/12
OK5_SHOPRITE 2015/01/10
OK5_SALDANHA 2014/12/23

The results should be ordered by DateChangedOn.

View 6 Replies View Related

SQL Server 2008 :: Adding Columns To Table Type?

Jul 24, 2015

Is there a good way to add columns to a table type?

I built several procs which make use of table-valued-parameters, and they work pretty nicely, until I need them to accept additional columns. Then I have to drop all the procs that use them, alter the types, and rebuild all the procedures, which is a huge pain in the rear.

Is there any good way (built in, or custom) to alter the def of a table type that's used as a parameter to multiple stored procedures?

View 9 Replies View Related

SQL Server 2008 :: Changing Large / Existing Table To Sparse Columns?

Sep 21, 2015

I have some huge tables (think 200+GB for a single table) which are excellent candidates for sparse columns. The tables have many columns which are defined with decimal datatypes (13,2) with a large percentage of them (over 50% in most cases- some as much as 99%) being 0.00. Since this is very expensive in terms of storage my idea is to set all the 0.00 values equal to NULL then set them as sparse. Across 100 or so identical databases, I have 5 such tables, with 20-40 columns in each table.

1.) three steps for each column in each table in each db.

Step 1: update table to allow for nulls

Step 2: update tabe set column=null where column =0.00

Step 3 update table set sparse columns


Step 1: Create entirely new table with sparse column definitions

Step 2: copy entire table, transforming 0.00 to null for affected columns via SSIS

Step 3: drop original table, rename new table to original name

View 0 Replies View Related

SQL Server 2008 :: Query Joining Multiple Tables Getting Duplicates?

May 17, 2013

I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?

select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id

There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.

View 9 Replies View Related

SQL Server 2008 :: Joining Two Tables - Split Rows Into Column

Sep 29, 2015

I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.

This how the data is

Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample

This is what is expected...

Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')

[Code] ....

How I can implement this ? I believe if there are any numbers I need to split into two columns .

View 2 Replies View Related

Integration Services :: SSIS 2008 R2 - Add Columns To Existing Mapping With Destination DB Table

Sep 8, 2015

The only way to add a new column to an existing mapping that I know is to go to advanced editor and refresh. This however keeps only the default mapping (where the field names match), the rest is wiped out, so need to restore the mapping manually after that. Risky and annoying at the same time. Is there any alternative?

View 3 Replies View Related

Joining Columns

Jun 11, 2008

I have 3 columns with text (dates) 06 03 08. I want to join those columns into one column.

Help, cast or concat does not work.

Lisa Jefferson

View 18 Replies View Related

Joining Tables - Adding Columns With Criteria

Oct 10, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. the columns will be added by month criteria. I want to keep all the records in a, and join columns from b. I do not want to loose any row from a if there is no data for that row in b.

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a:

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]

[Code] ....

View 6 Replies View Related

SQL Server 2008 :: Using Decimal Columns In SUM Function

Jul 21, 2015

Recently I have come across a requirement where i need to design a table.

There are some columns in table like below with DECIMAL Datatype:




Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

Now as per MSDN,

Precision => 1 - 9
Storage bytes => 5

so i can create column as:

BldgLengthDECIMAL(6,2) DEFAULT 0


BldgLengthDECIMAL(9,2) DEFAULT 0

Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

So my question is what should be value of DataType for above column ?

View 8 Replies View Related

SQL Server 2008 :: Calculating Median Over 2 Columns?

Aug 13, 2015

I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)

Example DB:

Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359

and I need it to display on SELECT as

column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359

Is this possible on 2008 R2?

View 7 Replies View Related

SQL Server 2008 :: How To Use Like Operator To Get Multiple Columns

Sep 12, 2015

how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

View 2 Replies View Related

SQL Server 2008 :: Transpose Columns To Rows?

Oct 29, 2015

I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.

I have data in the form of:

Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,

The output should look like:

Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black

View 2 Replies View Related

SQL Server 2008 :: How To Encrypt Either Columns Or Tables

Nov 2, 2015

how to encrypt a either columns or tables in SQL server (2008 R2).

Do I need to encrypt a whole table of can I encrypt certain columns of a table ?

View 2 Replies View Related

SQL Server 2012 :: Joining Table To Itself To Get Most Recent Date As Well As Additional Fields

Mar 11, 2014

I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:

(A = Active, R = Repeat, L = liquidation......)

Here is a sample of the data I am trying to report off of:

CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL


I have tried join to the same table - but I am still unable to get it to work.

View 1 Replies View Related

SQL Server 2008 :: Exclude Columns When Using Bulk Insert?

Dec 28, 2013

I'm able to successfully import data in a tab-delimited .txt file using the following statement.

BULK INSERT ImportProjectDates FROM "C: mpImportProjectDates.txt"

However, in order to import the text file, I had to add columns to the text file to match the columns that exist in the table. The original file is an export out of another database and contains all but 5 columns from my db.

How would I control which column BULK INSERT actually imports when working with a .txt file? I've tried using a FORMAT FILE, however I kept getting errors which I tracked down to being a case of not using it with a .txt file.

Yes, I could have the DBA add in the missing columns to the query from the other DB to create the columns, however I'd like to know a little bit more about this overall.

View 9 Replies View Related

SQL Server 2008 :: InPlace Update For Included Columns

Oct 8, 2014

The Delete-Insert update happens for key columns of Indexes and that makes sense.

But I am confused that why Delete-Insert update happens for Included columns of nonclustered indexes, where I expected them to be InPlace updates ???

View 3 Replies View Related

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

SQL Server 2008 :: IPAddress - How To Split One Column Into Two Columns

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of

values like below,Fe80::7033:acba:a4bd:f874,Fe80::7033:acba:a4bd:f874,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throw some error.

select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0


Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

View 1 Replies View Related

SQL Server 2008 :: Build A Query That Put Different Types In Columns

Jun 5, 2015

I have 3 tables, a supplier table, a types table and a relationship table between the two..I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.

CREATE TABLE #Suppliers(
id INT,
nam NVARCHAR(50)


It's possible to do this in "one" query?

View 2 Replies View Related

SQL Server 2008 :: How To Change Columns Based On Another Field Value

Jun 10, 2015

I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.

DECLARE @MyVari varchar(20)
SELECT [ProductID]

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Dynamic Conversion Of Pivot Columns

Jun 15, 2015

I have attached SQL File which Gives me the below resultset Excel.xlsx

But the problem is i am not able to round off the dynamic columns in side my PIVOT, how to rewrite the dynamic query.

View 4 Replies View Related

SQL Server 2008 :: Dated Columns With Drill Report?

Jul 4, 2015

I have a matrix report with STORES in the row group and DATES in the column group. The table sums on SALES. The DATES column is formatted like =format(Fields!DATES.Value, "MMM yyyy"). The table also has 2 parameters @Start and @End. This all works great but I then added a child report so that the user can click on the SALES value for any sale by month and store. The child report uses the @Start and @End parameters from the original report but this is where I run into problems.

Rather than bringing me the sales details for a particular store and month it brings back everything from the time period selected with the original date parameters. So say I originally selected 2015-01-01 to 2015-06-30 with the parameters when I select on FEB 15 in my matrix report I get Febs data along with all the other months ie Jan-Jun 15. The DATES fields in both reports are in the same date format - in fact both reports use exactly the same dataset.

I realize it's something to do with the formatting of the DATE field not being recognized in the linked report.

View 5 Replies View Related

SQL Server 2008 :: Creating Index Including Non-key Columns

Jul 9, 2015

Does including non-key columns work for the performance of an index?

View 8 Replies View Related

SQL Server 2008 :: Query To Fall Back On Different Columns

Oct 28, 2015

I have a person table

PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int

Assume I have data like this

INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(2, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(3, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(4, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(5, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(6, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(7, 'Ray', '01/23/2012', 0, 0)

I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person

For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.

For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person

Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.

If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.

I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.

I have tried writing various conditions but couldn't achieve a query that meets all my requirements here.

I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).

View 4 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

SQL Server 2008 :: Split Comma Separated String Into Columns?

Apr 24, 2015

Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.

A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.

The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.

I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.

This needs to be repeated for each row

, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest



View 8 Replies View Related

SQL Server 2008 :: Job Activity Monitor - Swapping Order Of Columns

May 28, 2015

Is there a way to permanently change the order of the columns in Job Activity Monitor?

I'd like to move Duration to the right of Step Name, but this only lasts so long as I have JAM open. Once I close it and re-open, JAM goes back to its default column order. Google gives me nothing but the temporary "drag and drop" method that I already know about.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved