Joing Multiple Columns

Feb 23, 2006

Hi All,

Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,

I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this

What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.

Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do

C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.

Any Advice would be fantastic.

Many Thank

Ian

View 1 Replies

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

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

Help Joing 4 Tables.

Mar 11, 2008

I need a bit of help writting a select statment and joining 4 differant talbes. I have used inner joins between two different tables before but never more then two tables.

Table layout (listing only the columns I want to pull):

Abbrevations.Short
Abbrevations.Long

Avatars.Creator
Avatars.AvatarName
Avatars.FileName
Avatars.Game
Avatars.Section
Avatars.Width
Avatars.Height

FanInfo.Crator
FanInfo.ContactMethod
FanInfo.Address

FanRattings.Cration
FanRattings.Rating

How they relate:
Abbrevations.Short = Avatars.Game
Avatars.Creator = FanInfo.Creator
Avatar.AvatarName = Ratings.Cration

Any help would be really helpful. Thanks

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

View 3 Replies View Related

Performance Joing To Same Table More Than Twice

Apr 30, 2007

I find that joining to the same table twice is OK, but as soon as you do it 3 or more times you get a massive performance hit.
Does anyone know the reason for this? Whats special about 3?
What's the best approach to do this sort of thing?
(I've used the SQL Server 2005 Tuning Advisor to add indexes for the query).

Rather than:
Select ..., sum(a1.<column>), sum(a2.<column>), sum(a3.<column>) from master_table
left join table_1 a1 on ...
left join table_1 a2 on ...
left join table_1 a3 on ...
group by ...

I have to select all the table and filter it using case:
Select ...,
sum(case when table_1.<column> = '...') as a1,
sum(case when table_1.<column> = '...') as a2,
sum(case when table_1.<column> = '...') as a3,
from master_table
left join table_1
group by ...

View 1 Replies View Related

Help Needed In Joing Tables

Apr 2, 2008

hi,

i have a table(Measurement) with the following columns
SensorId,Maximumvalue,MinimumValue,GroupId,SystemId,DeviceId.

so in the above column SensorId is ForeignKey of the Table Sensor which havs two columns(SensorId,SensorName)

so in the above column GRoupId is ForeignKey of the Table Group which havs two columns(GroupId,GroupName)

SystemId is ForeignKey of the Table System which havs two columns(SystemId,SystemName)

DeviceId is ForeignKey of the Table Device which havs two columns(DeviceId,DeviceName)

so my result should be as like as follows:

SensorId,SensorName,Maximumvalue,MinimumValue,GroupName,SystemName,DeviceName.

instesd of displaysing GroupId, SystemId,DeviceId i need to diplay the SensorName,GroupName,SystemName,DeviceName. so please give me some example query to join multiple tables please

View 2 Replies View Related

Joing Tables Using More Than One Field

Mar 21, 2006

I have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciar�n

View 2 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Apr 23, 2008

Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which

So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!

View 3 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Joing 2 Tables On An Encrypted Column

Mar 18, 2008

Hi all,
I am trying to join 2 tables in my database on an encrypted column. I am using a symmetric encryption, but because of the IV, a string encrypted at 2 different times produces 2 different encrypted strings. Therefore, I can not merely join the 2 tables on the encrypted columns. Has anyone any insight on techniques for solving this problem? The most naïve way, would be to decrypt both tables, join them, and re-encrypt them again. But, the tables are quite large, and this would be very processor (and time) intensive.
Any help would be greatly appreciated. Thanks in advance for any suggestions.

View 3 Replies View Related

Best Practice: Primary Key In Joing Table

Jan 26, 2006

hi there,

i have the following joining table (many-to-many relationship)...

CREATE TABLE [dbo].[products_to_products_swatch] (
[products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[product_id] [int] NOT NULL ,
[products_swatch_id] [int] NOT NULL
) ON [PRIMARY]
GO

question: do i need to include a primary key in this table - being that it is a joing table?

thanks
mike

View 2 Replies View Related

Calculated Columns Based On Multiple Columns?

Aug 12, 2014

MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers

View 5 Replies View Related

Search In Fulltextindexes For Multiple Searchterms In Multiple Columns

Mar 23, 2007

I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.

For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.

I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms

I managed to find a workaround:

SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.

Does anyone know a better solution?

Thanks in advance Bart Rouw

View 2 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Parent Table - (multiple) Rows Into (multiple) Columns

Feb 12, 2015

I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:

I need results for each parent like this

ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]

View 1 Replies View Related

Processing Multiple Rows And Multiple Columns In SSIS

Sep 26, 2007

I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!

View 1 Replies View Related

Tricky Query For Joing Master-detail Tables

Feb 28, 2002

I need to write a sql query which is a master-detail query. Here's the example structure of tables:

Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint

Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc

From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).

My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.

Many thanks,
Leonard

View 1 Replies View Related

Deleting Multiple Columns From Multiple Objects

Apr 2, 2008

Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.

View 2 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

Multiple Rows To One Row And Multiple Columns

Sep 7, 2007

Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:

This is the how the table is:
EmpID Designation

678
CFA

679
CFA

680
CFA

685
CFP

685
CIMA

685
IMCA

I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA

could anyone provide some help on this?

Thanks

View 1 Replies View Related

SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15

[code]...

Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.

View 0 Replies View Related

SQL 'Having' On Multiple Columns?

Mar 12, 2008

Can I write a having statement for multiple columns? Here's my situation: I want to select duplicates from a table based off of 3 fields:Normally one would use HAVING COUNT(*) > 1however, I need to INSERT INTO my table based on duplicates of the 3 fields but also insert the key from the first table, ie:insert into #TempTable (key, field1, field2, field3)select key, field1, field2, field3 from Table1 order by field1, field2, field3 Having COUNT(field1)>1 and COUNT(field2)>1 and COUNT(field3)>1 My question is this: Will this having statement compare all three fields of this row to all three fields of the other rows, or does it do each column independantly?for example:row 1: a b g row 2: a c k row 3: j c k These rows aren't the same, even though there are 2 a's, I want it to look at the entire row, and not return all three of these.

View 1 Replies View Related

Multiple Columns From WHERE?

Aug 10, 2004

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)

View 11 Replies View Related

Max Across Multiple Columns

Mar 13, 2015

I have a table that has 565789 distinct rows and I am tring to get the maximum value across several columns. This is an example of my data:

mid value1 value2 value3 value4 value5 value6
111 .034 .005 .036 .010 .012 .002
222 .054 .032 .001 .002 .005 .006
333 .002 .004 .006 .001 .003 .087

The query I am trying is: and it states invalud from but not sure why

SELECT MID
(SELECT MAX(VAL) FROM
(
SELECT A.VALUE1 FROM A UNION ALL
SELECT B.VALUE2 FROM B UNION ALL

[Code] ....

View 2 Replies View Related

CSV To Multiple Columns

Apr 27, 2006

If the table is denormalised and has Comma Seperate Values in a column, this code will copy it to Multiple columns of a Normalised table

declare @DeNormalisedTable table(data varchar(8000))

insert into @DeNormalisedTable
select '1,Davolio,Nancy' union all
select '2,Fuller,Andrew' union all
select '3,Leverling,Janet' union all
select '4,Peacock,Margaret' union all
select '5,Buchanan,Steven' union all
select '6,Suyama,Michael' union all
select '7,King,Robert' union all
select '8,Callahan,Laura' union all
select '9,Dodsworth,Anne'

select * from @DeNormalisedTable -- Comma Seperated Values

declare @s varchar(8000), @data varchar(8000)
Create table #NormalisedTable (Code int, FirstName varchar(100), LastName varchar(100))

select @s=''

while exists (Select * from @DeNormalisedTable where data>@s)
Begin
Select @s=min(data) from @DeNormalisedTable where data>@s
select @data=''''+replace(@s,',',''',''')+''''
insert into #NormalisedTable
exec('select '+@data)
End

select * from #NormalisedTable -- Data in Normalised Table

drop table #NormalisedTable

Madhivanan

Failing to plan is Planning to fail

View 4 Replies View Related

Max Of Multiple Columns

Jul 16, 2007

hi guys,

I have a table which has 4 columns A,B,C,D. it is somewhat like this:

A B C

S U 8
S U 10
S U 3
s V 14
S V 36
T U 25
T U 34
T U 9
T V 5
T V 1

This is the way i have in my database. I need to check which is the max in column C for values in Column A and Column B. First i need to start from column A. If it is S, then i need to see column B, if it is U then max[Column C] for only U & S. then when it becomes V, I need to get max [Column C] for only V & S. So on and so forth. So finally my output should be:

S U 10
S V 36
T U 34
T V 5

How can i do this?

regards,
David

View 8 Replies View Related

MIN/MAX Across Multiple Columns

Jul 25, 2007

I have seen questions posted a number of times where someone wants to find the maximum or minimum value from a set of columns in a single row of a table.

This script demonstrates two methods for finding the maximum value in a row across a set of columns in the row when any or all of the columns are allowed to be null or equal.

Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery.

Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up. I think 5 or 6 columns may be about the limit of complexity of coding that you would want to take on. One advantage of this script is that you can use the simpler to code Method 1 to test the more complex code for the Method 2 if you choose to implement it as a CASE statement.

If you have another method you would like to contribute, feel free. Also, if anyone wants to post performance test results, that would be nice.

print 'Create table to hold test data'
create table #t (
number int not null primary key clustered,
Val1 int,
Val2 int,
Val3 int,
Val4 int
)
GO
print 'Load test data'
insert into #t
select
number,
-- Generate random numbers
-- with about 1/7th null
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end,
case
when abs(checksum(newid()))%7 = 0
then null
else checksum(newid())%1000000
end
from
-- Load one million rows of test data.
-- Number table function here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(1,1000000)
go
print 'Find rows that do not match for Method 1 and Method 2'
select
out1.*,
out2.*
from
(
-- Method 1, using a subquery with a max
select
a.number,
a.Val1,
a.Val2,
a.Val3,
a.Val4,
[Max_of_Val1_to_Val4] =
(
select
X1= max(bb.xx)
from
(
select xx = a.Val1 where a.Val1 is not null union all
select xx = a.Val2 where a.Val2 is not null union all
select xx = a.Val3 where a.Val3 is not null union all
select xx = a.Val4 where a.Val4 is not null
) bb
)

from
#t a
) out1
join
(
-- Method 2, using a case
select
a.number,
a.Val1,
a.Val2,
a.Val3,
a.Val4,
[Max_of_Val1_to_Val4] =
case
when a.Val1 is not null and
(a.Val1 >= a.Val2 or a.Val2 is null) and
(a.Val1 >= a.Val3 or a.Val3 is null) and
(a.Val1 >= a.Val4 or a.Val4 is null)
then a.Val1
when a.Val2 is not null and
(a.Val2 >= a.Val1 or a.Val1 is null) and
(a.Val2 >= a.Val3 or a.Val3 is null) and
(a.Val2 >= a.Val4 or a.Val4 is null)
then a.Val2
when a.Val3 is not null and
(a.Val3 >= a.Val1 or a.Val1 is null) and
(a.Val3 >= a.Val2 or a.Val2 is null) and
(a.Val3 >= a.Val4 or a.Val4 is null)
then a.Val3
when a.Val4 is not null and
(a.Val4 >= a.Val1 or a.Val1 is null) and
(a.Val4 >= a.Val2 or a.Val2 is null) and
(a.Val4 >= a.Val3 or a.Val3 is null)
then a.Val4
else null
end
from
#t a
) out2
on out1.number = out2.number
where
-- Look for results that do not match
(out1.[Max_of_Val1_to_Val4] is null and out2.[Max_of_Val1_to_Val4] is not null) or
(out1.[Max_of_Val1_to_Val4] is not null and out2.[Max_of_Val1_to_Val4] is null) or
out1.[Max_of_Val1_to_Val4] <> out2.[Max_of_Val1_to_Val4]
go
print 'Find count of rows with different columns null'
print 'Should have a rowcount of 16 to test all conditions'
select
Null_Column_Conditions =
case when Val1 is null then 0 else 1000 end+
case when Val2 is null then 0 else 0100 end+
case when Val3 is null then 0 else 0010 end+
case when Val4 is null then 0 else 0001 end,
count(*)
from
#t
group by
case when Val1 is null then 0 else 1000 end+
case when Val2 is null then 0 else 0100 end+
case when Val3 is null then 0 else 0010 end+
case when Val4 is null then 0 else 0001 end
order by
1
go
drop table #t

Results:

Create table to hold test data
Load test data

(1000000 row(s) affected)

Find rows that do not match for Method 1 and Method 2

(0 row(s) affected)

Find count of rows with different columns null
Should have a rowcount of 16 to test all conditions
Null_Column_Conditions
---------------------- -----------
0 395
1 2444
10 2560
11 14760
100 2400
101 14955
110 14843
111 90206
1000 2518
1001 14857
1010 14989
1011 90256
1100 15100
1101 89659
1110 89783
1111 540275

(16 row(s) affected)

CODO ERGO SUM

View 5 Replies View Related

Multiple Key Columns?

Jun 11, 2007

I've been working on this project for a few months now. This is my first SS2005 project. I'm using SSIS, SSAS, and SSRS. I deal with volume statistics on labratory tests. The three main identifying columns of a test are: Bill_Item_Id, Mnemonic, and [Test Name]. My main dimension table uses the primary key column: Bill_Item_ID. I recently learned that I can't use this column as a primary key because there are a couple tests that have the same Bill_Item_Id, but different Test Names. I know that I can't have multiple primary keys, but is there a way I could say that no two rows in this table will have the same Bill_Item_ID, Mnemonic, AND [Test Name]? Then my fact table would have all three columns and match on those three instead of just one. I realize this will take a lot of work to rework SSAS and SSIS to accomadate for this, but unfortunately no one brought this tidbit of information to my attention.

Thank you very much.

View 4 Replies View Related