I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 RepliesI have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
I have a query which I want to convert It PIVOT query
SELECTÂ Â Â Â Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROMÂ Â Â Â Â Â Â Â CashSalesDetail INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Parties ON CashSales.PartyID = Parties.PartyIDWHEREÂ Â Â Â (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName
following is my requirement after summing up qty of each area
ProductName     area a        area b      area c
abc                         10               0              Â
20
def                          1                Â
4Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2
ghi                          5                Â
3Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
jkl                            7               Â
15Â Â Â Â Â Â Â Â Â Â Â Â Â 3
Note: numeric values are Quantity of each product in each area
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". Â The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". Â I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
View 3 Replies View RelatedSELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies View RelatedI have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
I want to append vertical row result into horizontal
CREATEÂ TABLEÂ #mable(mid INT, token nvarchar(16))
INSERT INTO #mable VALUES (0, 'foo')
INSERT INTO #mable VALUES(0, 'goo')
INSERT INTO #mable VALUES(1, 'hoo')
INSERT INTO #mable VALUES(1, 'moo')
Actual output
[code]....
i have a table like below,
CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME
[code]....
and then i pivot table by date as column wise using the below query and also displays total time by rowswise
SELECT t1.*, t2.Total
FROM (
SELECT Â name,[2015-08-01],[2015-08-02]
FROM (
SELECT Â name, AttDate,PresentTimeÂ
[code]....
now what i need is to display sum of time at last row as well, means total time of against date
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
Have a table with a structure like:
FIELD_A
FIELD_B
FIELD_C
FIELD_D
AMOUNT
DATA_A1
DATA_B1
DATA_C2
DATA_D2
37540
[Code] ....
In such a case, devising a dynamic cross-tab query, to cover all the items, and result like a pivot like the following to represent the data in a multi-header pivot like following:
DATA_C1
DATA_C1
DATA_C1
DATA_C2
DATA_C2
DATA_C2
[Code] ....
I am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?
This syntax is not displaying verified OR timespentinclass accurately. Where did I miss syntax?
DECLARE
@studentid varchar(50)
SET @studentid = 'ALL'
declare
@count int,
@course varchar(100),
[Code] ....
Hi,I was wondering if someone can help me. I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them. The columns inside them have the exact same design. For purposes sake called 'column1', 'column2', 'column3' and 'column4'. Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records. Can someone specify the syntax I need for this. I'm pretty sure it's a union query I need so am scouring as I type this also. Many Thanks Robert
View 5 Replies View RelatedI created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.
However, I can't figure out how to use it?
For example
select functiona (parameter) as data_src
this returns the "from" statement above
I then try to run
select * data_src
So how do I reference the contents of data_src in the select?
Thanks for any help
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008
;WITH CTE AS
(
SELECT
Case WHen left(MONTHDate,3)='Jan' Then '01'
WHen left(MONTHDate,3)='Feb' Then '02'
WHen left(MONTHDate,3)='Mar' Then '03'
WHen left(MONTHDate,3)='Apr' Then '04'
WHen left(MONTHDate,3)='May' Then '05'
[code]....
I have a dataset which is like:
Month, Day, Location, TotalSales
Jan    1     A 100Â
Jan    1     B 200Â
Jan     14    A 120Â
Feb     2     A 130Â
Mar     5     B 150Â
I want to transform the dataset using sql query into the following format:
Month, Day, LocationATotalSales, LocationBTotalSales, TotalSales
Jan    1       100            200              300
Jan    14      120             0               120
Feb    2       130             0               130
Mar    5         0           150              150
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',Â
@profile_name =
'SQL SMTP',
  Â
[code]....
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
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
[Code] ....
Desired Result:
esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1
OR
esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap
How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ). Â
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person. Â
SELECTÂ
 CASE GROUPING([Source])
         WHEN 1 THEN 'ALL'
         ELSE [Source] END AS 'Input Source',      Â
         avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'Â
                   Â
FROM Â Â Â Â Load_Time_Capture
WHERE Unprocessed_Capture >= Â DateADD(mi, -15, Current_TimeStamp)
GROUP BY Â Â [source] WITH ROLLUP
I need to create query for last stock quantity.
I have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.
Stores
London
Paris
Prague
Dates
1.1.2014
2.1.2014
3.1.2014
Transactions
1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
3.1.2014 Prague 1200
And result should look like this Last_Quantity should be Quantity for last date in Transactions table.
1.1.2014 London 1000
1.1.2014 Paris 1300
1.1.2014 Prague 1500
2.1.2014 London 800
2.1.2014 Paris 1300
2.1.2014 Prague 1500
3.1.2014 London 800
3.1.2014 Paris 1300
3.1.2014 Prague 1200
I am trying to create a query that will get data from two diffrent SQL servers. I am trying to link the two servers by using the sp_addlinkedserver. This is the code I use:
USE master;
GO
EXEC sp_addlinkedserverÂ
  N'SC00SRVERP501R1',
  N'SQL Server';
GO
and it executes successfully. But when I try to run my query I get the error message "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'."As far as I understand I need to provide some credentials to the linked server, but how do I do that ?
IÂ just started learning HierarchyID and copied the the following code from [URL] .... and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):
-- shcHierarchyid_1.sql
-- 19 August 2015 13:15 PM
USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
DROP DATABASE shcDB
GO
CREATE DATABASE shcDB
GO
[Code] ....
I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012). It worked nicely. But, I don't understand the following 2 things:
#1: In the Results, I got:Â Â Â Â Â Â Â Â Â Â
(No column name)    Position   Title
               1      /         CEO
                2 /1/        0x58      Purchase Manager
                3     /1/1/        0x5AC0 Purchase Executive
                4     /2/             0x68      Sales Manager
                5 /2/1/         0x6AC0 Sales Executive
I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?)Â I guessed). How I can give a column name to that column.
#2:  The book lists "USE master GO .....". I wonder whether I can do the "hierarchyid" thing in the private database I created!!?? Whether I can do the "hierarchyid" thing in the private database or not.
I have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.
View 6 Replies View RelatedIs there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 Replies View RelatedIs there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT *
 FROM Table
 WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"
We are having trouble figuring out how to create a view for this scenario:
We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.
Here is a sample of the data:
Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
So my view should result in :
Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
Need to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies View RelatedIs there a way i can grant a windows authenticated user say abcadam privilege:
-> to create, execute and alter the stored procedures that will be created by him only:
Point is we do not want the user to execute alter or modify other stored procedures out there for that DB