MS SQL Server :: Multiple Columns As The Pivot Key

Pivot Multiple Columns
I have a table the records the results of three different tests that are graded on a scale of 1-7. The table looks something like this.
PersonId          TestA   TestB   TestC

1                      4          5          4
2                      6          2          4
3                      5          5          6
4                      1          5          1

I would like to have a SQL statement that would pivot all this data into something like this

Test      1          2          3          4          5          6          7
A         1          0          0          1          1          1          0
B          0          1          0          0          3          0          0
C         1          0          0          2          0          1          0

Where the value for each number is a count of the number of people with that result.

The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together. Is there a way to do this in a single statement?

(If this has already been covered I apologize, but I could not find the solution.)

View Replies ! View Related

PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns
Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount

1
1
Panel
Jan
5
10
50

1
1
Panel
Feb
5
15
75

1
1
Panel
Mar
5
20
100

1
2
Frame
Jan
10
30
300

1
2
Frame
Feb
10
25
250

1
2
Frame
Mar
10
20
200

1
3
Glass
Jan
20
10
200

1
3
Glass
Feb
20
20
400

1
3
Glass
Mar
20
30
600

I would like it to be converted into following result set:

CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar

1
1
Panel
5
10
50
15
75
20
100

1
2
Frame
10
30
300
25
250
20
200

1
3
Glass
20
10
200
20
400
30
600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

View Replies ! View Related

Pivot On Dynamic Columns
I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.

I can get the rows needed:

guid1guid2guid3guid4guid5
================================
01100
01101

I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:

NAME Count
===========
guid10
guid22
guid32
guid40
guid51

I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:

SELECT NAME
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(#Temp)
ORDER BY COLID

NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.

I also don't want to use a cursor to construct a table.

Thanks for any help,
James

View Replies ! View Related

Using Two SUM Columns In A Pivot Table

I currently have a pivot table that is working great but I need to add to it. The below code is giving me the total ServiceTime per date, which is dynamic. I need to split this service time out depending on the stage of this note.

1) If the note has been signed
2) If the note has been signed and countersigned

SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as PendingServiceTime,

SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as ApprovedServiceTime

How do I add this to my pivot table query? I am thinking that I need to have two seperate queries and join them together some how.

SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,

ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime

FROM dbo.allNotes(8) AS t
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral'
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19)
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid
WHERE t.AgencyID = 8 AND t.tableName = 'collateral'
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL))
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

View Replies ! View Related

Invert Rows And Columns (PIVOT)
Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:

SellerID(PK)
Year(PK)
ItemsSoldDPT1
ItemsSoldDPT2
ValueSoldDPT1
ValueSoldDPT2

1
2002
10
20
300.00
400.00

1
2003
13
71
450.00
320.00

1
2004
8
4
350.00
640.00

1
2005
2
15
110.00
680.00

2
2001
3
1
130.00
100.00

2
2005
1
7
190.00
200.00

2
2006
6
9
170.00
500.00

...
...
...
...
...
...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:

SellerID
2001 Items
2001 Values
2002 Items
2002 Values
2003 Items
2003 Values
2004 Items
2004 Values
2005 Items
2005 Values
2006 Items
2006 Values
...

1
(NULL)
(NULL)
30
700.00
84
770.00
12
990.00
17
790.00
(NULL)
(NULL)
...

2
4
230.00
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)
8
390.00
15
670.00
...

...
...
..
...
...
...
...
...
...
...
...
...
...
...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

View Replies ! View Related

Help With A Pivot Table (dynamic Columns)
I have the following Pivot Table:

Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable

What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID     ProcessorType
23                    P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID    ProcessorType    Monitor Size
23                   P4M                   NULL
26                   NULL                  17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View Replies ! View Related

Grouping Columns In A Varaiable When Using PIVOT?

Hi,
I just read on web that we can not use grouping columns in a variable when using PIVOT operator. For example like,
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] ) // cannot put these in a variable like @Col
) AS pvt
ORDER BY VendorID;

Though it can be achieved using when making the query using dynamic sql. If some can make it clear why it is possible using dynamic sql and not with the above code.

Regards,

View Replies ! View Related

PIVOT With Dynamic Columns Names Created
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
     Division,
     [2] AS CurrentPeriod,
     [1] AS PreviousPeriod
FROM
     (
           SELECT
                   Period,
                   Division,
                   Sales_Amount
           FROM
                   Sales.SalesOrderHeader
           WHERE
                   (
                          Period = @period
                    OR    Period = @period - 1
                   )
     ) p
PIVOT
     (
           SUM (Sales_Amount)
           FOR Period IN ( [2], [1] )
     ) AS pvt

Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division    CurrentPeriod    PreviousPeriodA            400             3000 B            400              100 C            470              300 D            800             2500 E           1000             1900

What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.

     [2] AS CurrentPeriod,
     [1] AS PreviousPeriod

     FOR Period IN ( [2], [1] )

I have tried to use the @period but it doesn't work.

Thanks in advance.

Kenny

View Replies ! View Related

Attempting To Transpose/pivot Columns To Rows... I Think.
I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

Date______OrderNum____WorkDay

2006-06-1__AA_________1

2006-06-1__AB_________1

2006-06-2__BA_________2

2006-06-2__BB_________2

2006-06-2__BC_________2

2006-06-5__CA_________3

2006-06-5__CB_________3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1______Day2______Day3_______etc.

AA________BA_______CA_________etc.

AB________BB_______CB_________etc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

View Replies ! View Related

URGENT Plz ! Pivot Rows To Columns In SQL Server 2000
Hi,

I have the following data in a table called "Products" :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L

I need to group this data like this :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L

How can I do that ???

Thanks !

View Replies ! View Related

Pivot Multiple Values
Is there a way to pivot multiplie values in one 'run'.... In the order of ...

PIVOT ( SUM(DSH_TICKETS) FOR CPRF_NBR IN ([1], [2], [3], [4], [5])

SUM(HALL_CAPACITY) FOR CPRF_NBR IN ([1], [2], [3], [4], [5]) ) PVT

I know that there would be a problem with the headers, but that i could solve by using a second dummy for cprf_nbr and increase it with 10 (ex.)

Until knwo i did the jobg with a case statement, but it would be much nicer with a PIVOT.

View Replies ! View Related

Using PIVOT With Multiple Aggregates
I'd like to merge the 2 statements shown below into one. I'm wondering if there is a method of using PIVOT to get the SUM and COUNT aggregates in one statement. The only option I can get working is to use these as sub-queries but I'm hoping there is a better approach.

An answer would be great as would a better on-line resource than the BOL "Using PIVOT and UNPIVOT" topic.

Any guidance much appreciated.

/********

Get account type totals

********/

SELECT PVT.ACCOUNT_MANAGER_OID,

ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS',

ISNULL(PVT.[OTHER], 0) AS 'OTHERS'

FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,

1 AS 'REVIEW_IND',

CASE WHEN A.TYPE = ( 'Customer' )

THEN TYPE

ELSE 'OTHER'

END AS TYPE

FROM ACCOUNTS A LEFT OUTER JOIN

( SELECT ACCOUNT_OID,

1 AS [REVIEW_IND]

FROM dbo.ACCOUNT_HISTORY

WHERE TABLE_NAME = 'ACCOUNTS' AND

FIELD_NAME = 'REVIEW DATE'

) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID

WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL

) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT

ORDER BY PVT.ACCOUNT_MANAGER_OID

/**********

Get accounts review totals

***********/

SELECT PVT.ACCOUNT_MANAGER_OID,

ISNULL(PVT.[CUSTOMER], 0) AS 'CUSTOMERS_REVIEWED',

ISNULL(PVT.[OTHER], 0) AS 'OTHERS_REVIEWED'

FROM ( SELECT A.OID, A.ACCOUNT_MANAGER_OID,

1 AS 'REVIEW_IND',

CASE WHEN A.TYPE = 'Customer' THEN TYPE

ELSE 'OTHER'

END AS TYPE

FROM ACCOUNTS A LEFT OUTER JOIN

( SELECT ACCOUNT_OID,

1 AS [REVIEW_IND]

FROM dbo.ACCOUNT_HISTORY

WHERE TABLE_NAME = 'ACCOUNTS' AND

FIELD_NAME = 'REVIEW DATE'

) AS DRV_R ON DRV_R.ACCOUNT_OID = A.OID

WHERE A.ACCOUNT_MANAGER_OID IS NOT NULL

) A PIVOT ( COUNT(A.OID) FOR TYPE IN ( [CUSTOMER], [OTHER] ) ) AS PVT

ORDER BY PVT.ACCOUNT_MANAGER_OID

View Replies ! View Related

Pivot That Increases Columns Dynamically With Column Header As Boundary Date For Each Week
Hi All,

The current/ Base table would be like below,

Products

level

Date

N1

b

11/5/2007

N2

p

11/6/2007

N3

p

11/7/2007

N4

p

11/14/2007

N5

b

11/15/2007

N6

p

11/23/2007

Expected Result.

<=11/7/2007

<= 11/14/2007

<=11/21/2007

b

1

1

2

p

2

3

4

Total

3

4

6

As you can see, the above table has cumulative data.
1.            It calculates the number of Products submitted till a particular date- weekly
2.            The date columns should increase dynamically(if the dates in base table increases) each time the query is executed
For ex: the next date would be 11/28/2007
I tried something like, it gives me count of €˜b€™ level and €˜p€™ level products by week
declare @date1 as datetime
select @date1 = '6/30/2007'
while (@date1 != (select max(SDate) from dbo.TrendTable))
begin
set @date1 = @date1 + 7
                select Level, count(Products)
                from
                dbo.TrendTable
                where SDate < @date1
                group by Level
end
what I think is required is a pivot that dynamically adds the columns for increase in date range.
/Pls suggest if any other way of achieving it.
Pls help!!!

Thanks & Regards

View Replies ! View Related

Trouble With Pivot Task (multiple Problems) - Long
All,

I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting.

Background:
A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns.

Problem 1:
This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code)

Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail?

Problem 2:
When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates?

Problem 3:
When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more?

I am monitoring the package execution by:
1) logging all available output from SSIS to a file.
2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result.
3) Placing data viewers on all directional arrows from original db query and pivot

Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information.

Any help on one or more of these questions would be a great help...

Thanks.

-1071636293

-1071636293

View Replies ! View Related

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

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 Replies ! View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?
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 Replies ! View Related

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

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 Replies ! View Related

Search In Fulltextindexes For Multiple Searchterms In Multiple Columns
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 Replies ! View Related

Processing Multiple Rows And Multiple Columns In SSIS
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 Replies ! View Related

Deleting Multiple Columns From Multiple Objects
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 Replies ! View Related

Convert Multiple Rows Into Multiple Columns
I need to push multiple rows of data from one table to one record in another table. This can be achieved using cursors but I need to do them without using cursors.
Source Table:
fld_name fld_value
fld1 val1
fld2 val2
fld3 val3
fld4 val4

Destination should be like this:
fld1 fld2 fld3 fld4
val1 val2 val3 val4

Thanks.

View Replies ! View Related

SSMS Express: Using PIVOT Operator To Create Pivot Table - Error Messages 156 && 207
Hi all,

In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
   (SampleID int PRIMARY KEY NOT NULL,
  SampleName varchar(25) NOT NULL,
    AnalyteName varchar(25) NOT NULL,
    Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
           VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO

A desired Pivot Table is like:

                                              MW2          MW6S        MW7        TripBlank

Acetone                               1.00            1.00            1.00           1.00

Dichloroethene                    1.00            1.00            1.00           1.00

Trichloroethene                  20.00           1.00            1.00          1.00

Chloroform                           1.00           1.00             1.00           0.76

Methylene Chloride             1.00           1.00             1.00           0.51

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I write the following SQLQuery.sql code for creating  a Pivot Table from the Table dbo.LabData by using the PIVOT operator:

USE MyDatabase

GO

USE TABLE dbo.LabData

GO

SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank

FROM

(SELECT SampleName, AnalyteName, Concentration

FROM dbo.LabData) p

PIVOT

(

SUM (Concentration)

FOR AnalyteName IN ([1], , [11], [16])

) AS pvt

ORDER BY SampleName

GO

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I executed the above-mentioned code and I got the following error messages:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'TABLE'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'AnalyteName'.

I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.

Thanks in advance,

Scott Chang

View Replies ! View Related

Multiple Rows To One Row And Multiple Columns

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 Replies ! View Related

Pivot Task Error - Duplicate Pivot Key
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.

All worked fine, but now I get this error message:

[ytd_pivot [123]] Error: Duplicate pivot key value "6".

The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.

Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

View Replies ! View Related

SQL 'Having' On Multiple Columns?
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 Replies ! View Related

Multiple Columns From WHERE?
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 Replies ! View Related

Max Of Multiple Columns
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 Replies ! View Related

MIN/MAX Across Multiple Columns
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 Replies ! View Related

CSV To Multiple Columns
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 Replies ! View Related

Not IN With Multiple Columns

Is it possible to use multiple columns for a not in?
something like this;

Select * From MyTable
Where X , Y
Not In (Select X , Y FROM MyOtherTable )

If not, how would I accomplish this?

View Replies ! View Related

Multiple Key Columns?
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 Replies ! View Related

Pivot Example When You Don't Know The Exact Values To Pivot On
Say, I have the following temporary table (@tbl) where the QuestionID field will change values over time

Survey    QuestionID     Answer
1            1                   1
1            2                   0
2            1                   1
2            2                   2

I'd like to perform a pivot on it like this: select * from @tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable

...however, I can't just name the [1], [2] values because they're going to change.

Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])

I tried something like this:
for QuestionID in (select distinct QuestionID from @tbl)

but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @tbl Pivot (min(Answer) for Question_CID in (select distinct @QuestionID from @tbl)) as PivotTable

or does anyone know another way to do it?

View Replies ! View Related

Alter Multiple Columns In SQL
This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN (
HasValetParking varchar(1) NULL,
HasContinentalBreakfast varchar(1) NULL,
HasInRoomMovies varchar(1) NULL,
HasSauna varchar(1) NULL,
HasWhirlpool varchar(1) NULL,
HasVoiceMail varchar(1) NULL,
Has24HourSecurity varchar(1) NULL,
HasParkingGarage varchar(1) NULL,
HasElectronicRoomKeys varchar(1) NULL,
HasCoffeeTeaMaker varchar(1) NULL,
HasSafe varchar(1) NULL,
HasVideoCheckOut varchar(1) NULL,
HasRestrictedAccess varchar(1) NULL,
HasInteriorRoomEntrance varchar(1) NULL,
HasExteriorRoomEntrance varchar(1) NULL,
HasCombination varchar(1) NULL,
HasFitnessFacility varchar(1) NULL,
HasGameRoom varchar(1) NULL,
HasTennisCourt varchar(1) NULL,
HasGolfCourse varchar(1) NULL,
HasInHouseDining varchar(1) NULL,
HasInHouseBar varchar(1) NULL,
HasHandicapAccessible varchar(1) NULL,
HasChildrenAllowed varchar(1) NULL,
HasPetsAllowed varchar(1) NULL,
HasTVInRoom varchar(1) NULL,
HasDataPorts varchar(1) NULL,
HasMeetingRooms varchar(1) NULL,
HasBusinessCenter varchar(1) NULL,
HasDryCleaning varchar(1) NULL,
HasIndoorPool varchar(1) NULL,
HasOutdoorPool varchar(1) NULL,
HasNonSmokingRooms varchar(1) NULL,
HasAirportTransportation varchar(1) NULL,
HasAirconditioning varchar(1) NULL,
HasClothingAiron varchar(1) NULL,
HasWakeupService varchar(1) NULL,
HasMiniBarInRoom varchar(1) NULL,
HasRoomService varchar(1) NULL,
HasHairDryer varchar(1) NULL,
HasCarRentDesk varchar(1) NULL,
HasFamilyRooms varchar(1) NULL,
HasKitchen varchar(1) NULL,
HasMap varchar(1) NULL,
HasWiFi varchar(1) NULL,
GDSChainCode nvarvarchar(1) NULL,
GDSChainCodeName nvarchar(50) NULL,
DestinationID nvarchar(50) NULL,
DrivingDirections nvarchar(MAX) NULL,
NearbyAttractions nvarchar(MAX) NULL,
IANHotelID int NULL,HasClothingIron bit NULL
)
GO
COMMIT

View Replies ! View Related

Getting Back Multiple Columns
Hi, I have an asp.net app that essentially access, updates data in an sql server 2000.Instead of writing a stored procedure for each call to a table is there a way to write one stored procedure that grabs all the data from a table, then my vb code could parse through and find the data....Is this a stupid way, or should I stick to my original way... the problem is that I have hundreds of stored procedures....HUNDREDS... I don't think it's the way I am supposed to do this.redhanz.

View Replies ! View Related

Joing Multiple Columns
Hi All,Bit of a newbie question i'm afraid, so sorry if this is a reallystupid question,I am not sure if I am trying to do this in the right place as I am sureSQL Server has a far better way of doing thisWhat I am trying to do is join the contents of several columns andpresent the entry into a new column. the entries of all the columns Iam trying to join are different types such as dates and numbers. I wantto join all columns as a text string. To make it just a even moretricky I wish to insert a # between all fields.Just in case my poor description has got you thinking What the F*&Khere is an example of what I am trying to doC1 C2 C3 C4 C5Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890I have been trying to do this in the Formula in the design veiw of thetable.Any Advice would be fantastic.Many ThankIan

View Replies ! View Related

Multiple Rows And Columns MAX
Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.

View Replies ! View Related

INNER JOIN On On Multiple Columns
I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.

---------------------------

SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)

---------------------------

This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?

Another possible solution is if I were to use "WHERE/IN" like this:

---------------------------

SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)

---------------------------

This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.

I'm using SQL Server 2000 on Windows XP Pro.

View Replies ! View Related

Columns With Multiple Values ??
Hi,
The values I need to store in the table are

Student ID
Student Name
Subjects

The "Student ID" is the primary key.

A student can take more than 1 subject.

For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math

How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?

My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.

Or I can create a new field called "RowNumber" and keep that the primary key..
For example:

Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math

If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?

View Replies ! View Related

Add Multiple Columns In One Shot
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks

View Replies ! View Related

Multiple Columns Constraint
I need to setup a constraint to 2 columns(combine keys). Is there a way to accomplish this without using trigger?

View Replies ! View Related

Syntax For Multiple Columns In Contains
I was trying to find out if there is a way to check multiple
columns when using the CONTAINS condition in SQL 7.0.

I know i can do one column and I can do all the columns that
are full-text indexed, how do i do more than one but not all!

select * from table where contains (table.column1,'"foo1" and "foo2"')
and
select * from table where contains (table.column.*,'"foo1" and "foo2"')
do work...
but i need to do something like
select * from table where contains(column1,column2, '"foo"')

View Replies ! View Related

Multiple Columns From 1 Query
Hi, I have a query that gets the data for a specific week (starting Monday).

The below statement returns the data for the whole week mon-fri

What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.

How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1

SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1

FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed

Thanks in advance,
Tugsy

View Replies ! View Related

Updating Multiple Columns
Hi guys.........i'm tryign to update 2 tables in one stored procedure. However i'm getting errors. heres the code i have:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoUpdate]
GO
CREATE PROCEDURE dbo.snow_ors_additionalInfoUpdate
@Reference int,
@CanTravel int,
@SEEmployee varchar(100),
@SE_EMPLOYEE_FROM datetime,
@SE_EMPLOYEE_TO datetime,
@WorkHours Varchar(100),
@DrivingLicence varchar(100),
@CriminalConvictions bit,
@CriminalConvictionsDetails1 text,
@CriminalConvictionsDate1 datetime,
@CriminalConvictionsDetails2 text,
@CriminalConvictionsDate2 datetime,
@CriminalConvictionsDetails3 text,
@CriminalConvictionsDate3 datetime,
@VacancyMonitoring Varchar(100),
@VacancyMonitoringDetails Varchar(50),

AS
UPDATE Account
SET CanTravel= @CanTravel,
SEEmployee = @SEEmployee,
WorkHours= @WorkHours,
DrivingLicence = @DrivingLicence,
CriminalConvictions = @CriminalConvictions,
CriminalConvictionsDetails1 = @CriminalConvictionsDetails1,
CriminalConvictionsDate1 = @CriminalConvictionsDate1,
CriminalConvictionsDetails2 = @CriminalConvictionsDetails2,
CriminalConvictionsDate2 = @CriminalConvictionsDate2,
CriminalConvictionsDetails3 = @CriminalConvictionsDetails3,
CriminalConvictionsDate3 = @CriminalConvictionsDate3
WHERE Account.Reference = @Reference

UPDATE Application
SETVacancyMonitoring = @VacancyMonitoring,
VacancyMonitoringDetails = @VacancyMonitoringDetails,
WHERE Application.Reference = @Reference

GO

and here are the errors i'm getting:

Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 19
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Procedure snow_ors_additionalInfoUpdate, Line 37
Incorrect syntax near the keyword 'WHERE'.

thanks again guys!

View Replies ! View Related

Select Where LIKE From Multiple Columns
I am using mySQL and the following query works fine:

SELECT * from listings where name LIKE "%$trimmed%" order by name";

and so does this query:

SELECT * from listings where keywords LIKE "%$trimmed%" order by name";

however, I can't seem to combine the two with an OR statement as this query only returns the results from the first LIKE column

select * from listings where name LIKE "%$trimmed%" or keywords LIKE "%$trimmed%" order by name

I want to be able to search both columns and return a row if the NAME column or the KEYWORDS columns contains a string.

View Replies ! View Related

Search Multiple Columns
Hi

I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?

View Replies ! View Related

Selecting Multiple Columns Into One
I have a product table that has a productID column and a productName column. Then I have a productCategory table that associates productIDs with categoryIDs. And lastly I have a category table containing a categoryID and categoryName. I want to write a query that will return a table with three columns...productID, productName, and and calculated column containing the categories the product belongs to separated by a comma. A sample output would be...

ProductID____ProductName____Categories
1____________Green Flats____Footwear, Accessories, Women's Apparel
2____________Purple Belt____Accessories, Women's Apparel
3____________Pink Tunic_____Women's Apparel

If any of this isn't clear please feel free to write with questions.
So far I have this query -
SELECT Products.ProductID, ProductName, CategoryName FROM Products, ProductCategory, Categories WHERE Product.ProductID = ProductCategory.ProductID AND Categories.CategoryID = ProductCategory.CategoryID

This returns....
ProductID_____ProductName____CategoryName
1_____________Green Flats____Shoes
1_____________Green Flats____Accessories
1_____________Green Flats____Women's Apparel
2_____________Purple Belt____Accessories
2_____________Purple Belt____Women's Apparel
etc....

Any suggestions on how to remedy this???

View Replies ! View Related