T-SQL (SS2K8) :: How To Select Columns That Have Some Values Only

Jun 1, 2015

I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.

Here is an example:

IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),

[Code] .....

In this case, I'd like to run a query like this one(skip Column Event3):

Select eventId,Category,events1,events2,events4,events5 From #events

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Include Row Values As Columns In Select Query

Apr 28, 2015

How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.

Existing table layout

table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go

Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go

Once I figure out how to get the data in the layout above, I will need to join the table with my record table.

table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress

I would like to be able to join the tables in the query below for the final output.

Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID

How I can get the data in the desired #tempComment table layout mentioned above?

View 2 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Can It Change Columns Of A Table Values To Rows

May 14, 2014

I have a table with this info:

NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21

How I can get this result:

NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82

[code]....

Should I use unpivot or pivot?

View 2 Replies View Related

T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?

May 6, 2014

I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL

[code]....

So this would be the resultset:

1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27

View 3 Replies View Related

T-SQL (SS2K8) :: Select Asterisk Not Returning All Columns In UDF

May 13, 2015

I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.I looked at the UDF and it does a fairly simple select:

select a.*
from myTable A

The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.

The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.

View 5 Replies View Related

Values Of Two Columns In Two Different Tables--presentation Using Select

Mar 14, 2006

Values of two columns in two different tables--presentation usingselectHi Everyone,i have two tables in the database . One is called address tableand one is adressPhone Table. Below is the sample of those two tablesAddresscol1 col2 col3X 12 13y 15 19z 18 10create table address(col1 varchar(20),col2 int, col3 int)insert into address values ('x',12,13)insert into address values ('y',15,19)insert into address values ('z',18,10)AddressPhoneCol4 Col5 Col613 213-455-9876 113 415-564-6546 213 543-987-5677 319 678-555-2222 1create table addressphone(col4 int, col5 varchar(50),col6 int)insert into addressphone values(13,'213-455-9876',1)insert into addressphone values(13,'415-564-6546',2)insert into addressphone values(13,'543-987-5677',3)insert into addressphone values(19,'678-555-2222',1)I have to display something like thisx 12 13 213-455-9876 415-564-6546 543-987-5677y 15 19 678-555-2222 NULL NULLSo there is one to many relationship between address andaddressPhone table where address.col3 = addressphone.col4I don't know how to write the query to get the phone numbers thathas he same id in the same row like I displyed above.I did something like this, but this is not workingselect col1,col2,col3, (select col5 from addressPhone where col6=1),(select col5 from addressPhone where col6=2), (select col5 fromaddressPhone where col6=3),from address table inner join addressPhoneon address.col3=addressphone.col4above is not working because it is complaining that a subquerycannot return multiple results. Col6 in the addressphone table isthe phone type ike business phone,mobile phone or home phone. It is possible that there are two phonenumbers for business phone.Please let me know how can I write this query.Any help will be greatly appreciated.Thanks

View 4 Replies View Related

SELECT With NULL Values In One Or More Of Selected Columns

Apr 10, 2008

Hello everyone,

I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?


SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex

FROM Person LEFT OUTER JOIN

Person2Role ON Person.personID = Person2Role.personID

WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))

AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))

AND (Person.sex = ISNULL(@sex, Person.sex))

This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters


I am working with SQL Server 2005 Express Version

Thank you,

Alex

View 10 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

T-SQL (SS2K8) :: Aggregate Monthly Values From Interval Values?

May 14, 2014

I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.

Currently it is displaying.....

MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL

Output should display as

MeterId Period Reading

4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15

View 4 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

Jan 13, 2015

I've got some records like this:

ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0

where each month field has a 0 or 1, depending on if the person was enrolled that month.

I'm being asked to generate a table like this:

ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014

Is there some slam dunk way to do this without a bunch of If/Then statements?

The editor compressed all my space fields, so the column headers are off in some places.

View 8 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Lookup To Check Values In 13 Columns With Same Values

Mar 22, 2006

hi,

it is my first post on this forum, please be patient if i miss any important bit of information.

i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.

i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.

since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.

i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.


please let me know if further clarification is necessary.

regards,

nicolas

View 5 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related

T-SQL (SS2K8) :: Adding Columns On The Fly

Apr 21, 2014

I have a report that looks like the following

NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65

HOWEVER I need it to look like the following:

NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65

The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.

Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Data In 2 Columns

Oct 17, 2014

I am currently working on extracting data from a text file and loading into sql server.I have a table with 1 column and the data in the column is in this format:

TableName1
A
1
10

TableName2
D
2
20

I want to convert this to 2 columns in this format:

ColumnA ColumnB
TableName1 A
TableName1 1
TableName1 10
TableName2 D
TableName2 2
TableName2 20

View 1 Replies View Related

T-SQL (SS2K8) :: How To Sum Columns In Table

Dec 16, 2014

I've the table structure below. Example table of my original

create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime

[Code] ....

Expecting Output :

Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500

How to do that?

I tried like

select
k.fs_acoounts,
k.200Prod,
(
my query
)k

its showing error '200prod'

View 1 Replies View Related

T-SQL (SS2K8) :: Split Value Into Two Columns

Mar 30, 2015

I want to display full name column as FirstName and LastName.

Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav

i.e. Before Space is FirstName and After Space is LastName.

View 9 Replies View Related

T-SQL (SS2K8) :: BETWEEN Values Conundrum

Oct 23, 2014

I have a problem with trying to pull postcodes from a table when I have a lookup table which provides me with a StartPostCode and an EndPostCode.

For instance if, in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table.My problem is my query seems to bring two values back even if the postcode is between the ranges specified.To reproduce the problem first create the tables and populate with the data.

USE [CTSStaging]
GO
/****** Object: Table [dbo].[st_StobartPostCode] Script Date: 10/23/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Finding Maximum Value Out Of 5 Different Columns?

Jun 2, 2011

How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:

Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A

I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.

View 9 Replies View Related

T-SQL (SS2K8) :: Computed Columns - Max From Other Column For The Same ID?

Jul 25, 2012

Suppose a table being

Create table myTable (ID int, col1 int, col2 int)

I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".

Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?

Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...

View 9 Replies View Related

T-SQL (SS2K8) :: CTE Has More Columns Than Were Specified In Column List

Mar 22, 2014

I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list

--SQL CODE IS AS BELOW

WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Group Columns To One Row In A Table

Apr 1, 2014

I have a table with the following columns

Num,ID,Pos,Value
74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo

I am trying to get the result as follows.

Num Id Final Value

74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo

I tried to use Stuff and XMLpath but it is not giving me distinct results.

View 4 Replies View Related

T-SQL (SS2K8) :: Max With Distinct Two Columns And Corresponding Third Column

Apr 30, 2014

T1
-------
a1
a2
datetime
a4
a5
.....

i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.

create table abc_test (
id int
,runs int
,date1 datetime

[code]....

I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.

View 5 Replies View Related

T-SQL (SS2K8) :: Check Multiple Columns With One Value

May 21, 2014

I keep thinking this can be done in one query:UPDATE H

SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL

[Code] ....

I need to NULL any phone number in the Hold table where the number is in the Lookup table.

View 5 Replies View Related

T-SQL (SS2K8) :: Results Doubling Up In Two Columns?

Aug 12, 2014

I have written some SQL that relates to the (partial) result set below -

I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.

My query looks like the following -

go

declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'

[Code].....

View 1 Replies View Related

T-SQL (SS2K8) :: Way To Convert Rows To Columns

Sep 17, 2014

I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.

Sample data:

create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;

How do I use LEFT OUTER JOIN to produce result similar to the below?

SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from

[code]....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Compare Two Rows And Two Different Columns

Oct 22, 2014

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',

[Code] ....

View 6 Replies View Related







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