T-SQL (SS2K8) :: Case Query - Unpivot Function

May 6, 2015

I've this result from my 'case' query;

Jan Feb Mar April
1 2 3 4

I want ;

Month Value
JAN 1
Feb 2
Mar 3
April 4

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Pivot And Unpivot In Same Query?

Feb 15, 2014

I have below table and within same query i need pivot and unpivot.

create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp

Now i need output in below format

name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30

View 4 Replies View Related

T-SQL (SS2K8) :: Comparing Columns In Unpivot Table

Nov 21, 2014

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL

[Code] ....

I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:

[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]

Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline

update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

View 7 Replies View Related

T-SQL (SS2K8) :: Using Case Within PIVOT Portion Of Crosstab Query

Apr 13, 2015

I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:

ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast

1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0

I'm using this statement to create the result set:

SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription

[Code] .....

Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:

ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 X X X X X
1024 Item1024 X X X X X
135 Item135 X X X X X
107 Item107 X X X
106 Item106 X X X X X

I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?

View 6 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]

[code]....

View 3 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:

"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."

What is the cause of this issue?

Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,

[Code] ....

View 3 Replies View Related

T-SQL (SS2K8) :: Unpivot Sorting Column In Alphabetical Order Automatically

Jan 27, 2015

I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,

Here is my code

@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

Can You Unpivot On Multiple Keys With The Unpivot Transform

Oct 17, 2006

Hi all,

I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?

If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?

Thanks in advance.


Mark

View 2 Replies View Related

SQL: Unpivot Query

Dec 25, 2007

Hi,
I have data stored in a format like this...
RespondantID, Q1, Q2, Q3 ...




1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1

2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2
But want to convert it to something like...
SurveyID, RespondantID, QuestionID, Answer
1, 1 - Anonymous, Q1, 1
1, 1 - Anonymous, Q2, 1
...
1, 2 - Anonymous, Q1, 2
1, 3 - Anonymous, Q2, 2
...
 
Please can someone help.
Thanks!

View 1 Replies View Related

T-SQL (SS2K8) :: Case For Field Type

Jun 7, 2014

Is there an easy way to send back to my C# program the pascal case of a field type? If you get the parameters or columns for a table or procedure the type is in all lowercase (varchar instead of VarChar). But in C#, it is in an enum (SqlDbType) that has the variables as mixed case so you can't compare them and just pass the type. You need to figure out what the case should be - varchar should be VarChar and bigint should be BigInt.

View 1 Replies View Related

T-SQL (SS2K8) :: Using CASE Statement Within A PIVOT

Jun 17, 2014

I am using a PIVOT function to obtain the Invoice Values, but they appear in different currencies so need to perform a case function.

But am struggling with the syntax;

This fails a syntax check with
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'Case'.

[Code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Statement Using Case And Join Together

Jan 29, 2015

I am trying to use this logic into a query:

Select P.S,E.S,E.R
from Pack P(nolock)
join Exp E on P.Id=E.O
on E.R is null
case when E.R is not null then ''
else ''
end
where P.s='PLT000044'

I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.

View 2 Replies View Related

T-SQL (SS2K8) :: Case Statement In The Where Clause?

Jun 5, 2015

I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.

I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.

A small bit of the code:

declare @get_date datetime = convert(char(10),getdate(),101)
select top 10 pt.pt_id, pt.last_name
fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id
and a.pt_id = pt.pt_id
and a.from_file_ind = pt.from_file_ind

[Code] ....

Seems I should be able to not test the last name after the turnover date, but I can't figure out how.

View 2 Replies View Related

T-SQL (SS2K8) :: Using Case Statement In Pivot?

Jun 23, 2015

Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.

select (
Column1
,Column2
,Column3
,Column4
,coloumn5
from Mytable
) x
pivot
(
case when Column1 = 6 then sum(Column3) else max(Column4) End
for coloumn5 in (' + @COLS + ')
)p

View 2 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

T-SQL (SS2K8) :: How To Execute SP In Select Case Statement

Mar 20, 2014

I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.

Anyways, I have a table which is dba.dbhakyedek.

Columns are

dbname, username, class_desc, object_name, permission_name, state_desc

I have statement

select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'
end
from dba.dbhakyedek
where username='MC'

This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.

View 6 Replies View Related

T-SQL (SS2K8) :: Update With Case Statement Not Working?

May 29, 2014

I have a situation where I want to update a column if and only if it is null.

UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID

But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.

So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.

View 6 Replies View Related

T-SQL (SS2K8) :: Dividing Two Rows Within CASE Statement

Jul 28, 2014

I was given the task to come up with a result set based on certain criteria:

Please add one row for each offer code

1) Opt-in rate by offer code: This can be calculated by dividing XXX Inventory with lead / XXX Inventory (A)

The report should read something like below:

Example result set:

Log Date: OfferLetter OfferCode DailyCount

2014-07-20 A XXX Inventory (A) 108
2014-07-20 A XXX Inventory with lead 54
2014-07-20 A XXX Inventory Opt-in Rate: 50%

There are 12 different groupings and OfferLetter A is just one of them.

Below is the code that is written to date:

DECLARE @Start datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 8), 0)
DECLARE @End datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1) + 1, 0)
DECLARE @Today datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1), 0);

SELECTDT.OfferCode, DT.OfferCodeDesc
INTO#tempOfferCodes

[Code] ....

The issue I'm having is that the values I need to divide by are in fact, a result set from the CASE statement. It's been a long time since I've done anything like this.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Perform Mathematical Formula Without Using Case

Sep 17, 2014

I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.

I want to perform following without using case if it is possible.

When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty

And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.

Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.

View 9 Replies View Related

T-SQL (SS2K8) :: Using A Date Comparison In A Case Statement?

Mar 19, 2015

I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:

Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'

[Code] ....

When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.

Here is the full piece that the Case resides in:

Insert _Test
SELECT
EmpNo,
PersonIdNo,
REPLACE(PersonTaxIdNo,'-',''),
LastName,
FirstName,

[code]......

View 3 Replies View Related

Case Function In T-SQL

Sep 13, 2007

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:
datediff(dd,@startdate,@today) = @dDay
or convert(varchar(12), @startDate, 101) between convert(varchar(12), @today, 101) and convert(varchar(12), dateadd(dd,@dDay, @today), 101)
It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help
Any suggestion is welcome. thanks
 
 

View 6 Replies View Related

IIF Function Or Case

Nov 27, 2006

Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

My "Case"


USE SysproCompanyB
GO
SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
CASE
WHEN BuyMulDiv IS NULL THEN '0'
WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
ELSE 0
END
FROM dbo.ApSupplier INNER JOIN
dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

GO

View 6 Replies View Related

Case Function Again:(

May 9, 2008


Hi,

I'd like to ask my question with the data sample this time. Below is my table with 3 phone numbers, null, zero, less than 10 digits, I'd to get as an out put phone with not null, zero, or less than 10 digits .I couldn't run that CASE statement. Please HELP ......SOS.. Thanks






Home_phone
Work_phone
cell_phone_1

0
0
0

12344567890
0
0

0
NULL
32547821

12344567891
NULL
0

12344567892
NULL
0

0
0
12344567893

0
0
12344567894

0
0
NULL

12344567895
0
0

0
12345
0

12344567896
0
0


12344567897
0

Null
0
0

12344567898
0
0









OUTPUT



12344567890



12344567891



12344567892



12344567893



12344567894



12344567895



12344567896



12344567897



12344567898


View 7 Replies View Related

Case / Switch Function Help

Jun 9, 2004

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.

View 3 Replies View Related

Function Or CASE Statement?

Feb 25, 2008



Hi,

I have a Query some thing like this,

Select Table1.Code, 'field2' = CASE




WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code)
WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code)
WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code)
END

FROM Table1

Do I need to use Function instead of CASE for better performance ?


Regards

Mujeeb






View 5 Replies View Related

Create User Function W/Case

Jul 23, 2005

I keep getting an error message "incorrect syntax near keyword case"when trying to run this:USE DEDUPEGOCREATE FUNCTION fnCleanString(@mString varchar (255))RETURNS varchar(255)ASBEGINDECLARE@mChar char(1),@msTemp varchar(255),@miLen int,@i int,@iAsc intBEGINset @mChar = ''set @msTemp = ''set @miLen = Len(@mString)set @i = 1while @i <= @miLenbeginset @mChar = substring(@mString,@i,1)set @iAsc = Ascii(@mChar)casewhen @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mCharwhen iAsc >= 65 And iAsc <= 90 Then set @mChar = @mCharwhen iAsc >= 49 And iAsc <= 57 Then set @mChar = @mCharelse @mChar = ""endset @msTemp = @msTemp & @mCharset @i = @i + 1endENDRETURN @msTempENDCan anybody point out what I'm doing wrong?Thanks.Randy

View 3 Replies View Related

T-SQL (SS2K8) :: UDF Scalar Value Function

Jun 24, 2015

I build a UDF scalar function like this:

CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT

[Code] ....

Curiously when i call my function the same one return always the same value, ex:

Select VerificaAcessoPerfil(2)

the return value is : 698 ??

but if i run the Select statment like this:

SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC

The value are:

1695
2211
41
10698
11697
13696

View 9 Replies View Related

How To Recall Column Name Defined In CASE Function

Aug 17, 2007

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@PolicyType AS VARCHAR(10),
@ReportFrom AS DATETIME,
@ReportTo AS DATETIME,
@Business AS VARCHAR(1),
@Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType


WHERE
SLA = @Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate

View 3 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

Problem With PATINDEX Function For Case-sensitive Information

Aug 21, 2007

Hi,

My database is not case-sensitive, but I want output like...

SELECT patindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

View 7 Replies View Related

T-SQL (SS2K8) :: Date Format - Convert Function

Mar 12, 2014

How to get this out put.

Details:

declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM

declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert

--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM

What is the correct date format to achieve this.

View 2 Replies View Related

T-SQL (SS2K8) :: POWER Function And FLOAT Limitation?

Apr 2, 2014

I am just wondering why

SELECT POWER(2.718, -34.08)
returns me 0.

Tested it in Microsoft Excel

=2.718^-34.08

and it is returning me 1.58774E-15 which is approximate 15 decimal points.

My table column defined as FLOAT which can take up to 54.

Is it the limitation in the POWER function ?

View 3 Replies View Related







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