I have table like below.
filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y
I Need a output like this
filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2
I wanted to group the col1 , col2, col3 and group it as same group.
I have a query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)
[Code] ....
and so on.
I've tried putting my group by everywhere but it's not working. I'm missing something but what?
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.
However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?
Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, why one location is better than another location?
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
View 6 Replies View RelatedI am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
View 12 Replies View RelatedI am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other
View 3 Replies View RelatedI have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
I am having some difficulty getting a query to output an alpha numeric range grouping.
I have this data set:
Despatch_id Sample_ID
MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757
[code]....
Output:
DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL
[code]....
i'm building a query to return metrics that will drive 3 seperate pivot tables showing
1. Total count of LineItems per D_Type each month
2. Total count of LineItems per Status each month
3. Avg count of LineItems per Invoice each month
I am able to get the first two, but having hard time with the 3rd.
Here's some representative ddl
create table Remediation
(Invoice nvarchar(10), D_Type nvarchar(20), Status nvarchar(20), RemediationDate datetime);
insert into Remediation values
--this will create data for Jan, 2014
('501', 'Recycle', 'Pass', getdate()-30),
('501', 'Reuse', 'Pass', getdate()-30),
('501', 'Remarket', 'Fail', getdate()-30),
[code]....
how to add the average metric to this query?
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL
[Code] ....
EXPECTED RESULT:
Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
I am working on a migration project and need to derive the total length of the columns for a particular table. I have the following query designed for the same:
select
t.name,
sum(c.max_length)
from sys.all_columns c
join sys.all_objects t
on t.object_id = c.object_id
where t.name='test_1'
group by t.name
;
This query gives me correct results except for data types varchar(max), nvarchar and xml for which the max_length is stored as -1 which causes my results to be incorrect when summed up. Is there any other way to find out the maximum data length for such type of columns?
Here is my table DDL:
CREATE TABLE [dbo].[TEST_1](
[COL_1] [bigint] NULL,
[COL_2] [bit] NULL,
[COL_3] [char](10) NULL,
[COL_4] [date] NULL,
[code]....
I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.
I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
When I add three columns to a table, they always go to the end of it.
I want to add a column as the third of the table, the second column that I want to add needs to be at the fifth place.
How can I do this using t-sql?
Is this possible?
I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. What are the cause of the Index scan how can we change that to index seek?
I am giving that kind of similar query below
SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '
HI,
i have 2 columns named firstname and lastname, i need to get them into 1 column named name with a space between them.
Does anyone have a tip to do this?
Wimmo
Hi,I was trying to retrieve some data in such a way that it 2 columns willbe merged into one, with a column in between. I am trying to dosomething like this:SELECT LastName + ", " + FirstName AS NameFROM EmployeeTBLORDER BY LastNameBut SQL Server does not like this syntax (though it does work with"LastName + FirstName").I appreciate any help.Thanks,Aaron
View 4 Replies View RelatedHello guys !!
I'm actually a Mysql user, not a SQL Server user, just becouse the business I work uses it. But the problem I've had I think doesn't have any relation on the plataform it's running...
This is a construction software... I'll try my best to explain you : My table has 4 main columns (Face,Station,Combination,sAs). Face , Station and Combination form a primary key (never repeat together) and the "sAs" represents a calculus between some parameters.
The problem (it became a challenge already :D) consists in selecting the Face,Station and Combination where sAs is maximus, BUT grouping ONLY Face and Station.
For example :
Face Station Combination sAs
F1 0 Comb1 45
F1 0 Comb2 13
F1 0 Comb3 30
---
F1 10 Comb1 42
F1 10 Comb2 60
F1 10 Comb3 12
---
F2 0 Comb1 32
F2 0 Comb2 15
F2 0 Comb3 01
---
F2 10 Comb1 02
F2 10 Comb2 07
F2 10 Comb3 23
Here is the challenge :
If you execute the following query : "select Face,Station,Combination,max(sas) as sAS from test group by Face,Station" it returns you an arbitrary Combination for the rows in the resultset.
Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb1 60
F2 0 Comb1 32
F2 10 Comb1 23
But the combination I'd like to have is the combination related to the maximus sAs in F1/10, Comb2 instead of Comb1 returned..... The same occurs with the last row F2/10.
The query I wanna find should return the following resultset.
Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb2 60
F2 0 Comb1 32
F2 10 Comb3 23
Please, if somebody has any idea, share it to help me....
Thanks a lot..
Rodrigo
Some code to make it easier to try.... Maybe it has some difference between SQL Server and MySQL.
////////////// CUT HERE
create table test(
Face varchar(20),
Station int,
Combination varchar(20),
sAs int
);
insert into test values('F1', 0, 'Comb1', 45);
insert into test values('F1', 0, 'Comb2', 3);
insert into test values('F1', 0, 'Comb3', 30);
insert into test values('F1', 10, 'Comb1', 42);
insert into test values('F1', 10, 'Comb2', 60);
insert into test values('F1', 10, 'Comb3', 12);
insert into test values('F2', 0, 'Comb1', 32);
insert into test values('F2', 0, 'Comb2', 15);
insert into test values('F2', 0, 'Comb3', 01);
insert into test values('F2', 10, 'Comb1', 02);
insert into test values('F2', 10, 'Comb2', 07);
insert into test values('F2', 10, 'Comb3', 23);
select Face,Station,Combination,max(sas) as sAS from test group by Face,Station;
////////////// CUT HERE
Rodrigo Bornholdt
Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully
View 5 Replies View RelatedHere is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.
Code:
with cte
as
(
selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
,SUM(net_weight) as totalWeight, rate
,(select SUM(net_weight) * rate) as amount
[code]....
I am using sql table named as product which is having columns partno,partnm,weight,surfacearea,totalhr,type
I want sum of weight,surfacearea,totalhr and grouping on partno,partnm,type
If I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr) from product GROUP BY partno,partnm then its working correctly with sum and grouping but if I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr),type from product GROUP BY partno,partnm,type then it is not grouping as expected.
why if third column included in group by clause its not working correctly...Is there any way to group as I want.
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,
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.