Tag: sql

TSQL trim columns/table

I am importing to SSMS 2016 txt file (comma delimited, ” text qualifier) but within the text there is a lot of ” which just causing mess. So, I have imported this as comma delimited only, with no errors all looks good. Column(s) look like example: “Ab-123-2 ” “wheel 17″ ” “hello “world” ” Using […]

Extract inversed duplicated values sql

I have two tables: Likes (IDPerfilA, IDPerfilB, Data) Amizade (IDPerfilA, IDPerfilB, Data) *Amizade = Friends in English. My table likes is uploaded with many likes, with the constraint that does not allow that IDPerfilA likes the same IDperfilB (EX: user with the ID 4 con not like user with the ID 4). Now, I want […]

How to produce such results using SQL

I edited my question as it seems like people misunderstood what I wanted. I have a table which has the following columns: Company Transaction ID Transaction Date The result I want is: | COMPANY | Transaction ID |Transaction Date | GROUP |———————|——————|——————|———- | Company A | t_0001 | 01-01-2014 | 1 | Company A | […]

Querying a null boolean field

When querying a boolean field that is null why does Select * From MyTableName where [boolfieldX] <> 1 not return any rows with null in [boolfieldX]? 1 <> null I would have expected rows with null to be returned.

Create polygons based on GPS-logging points SQL 2008

I’m using SQL Server 2008 and I want to create a database view in which polygons are created based on points in a results-table? Some remarks: The results-table contains i.a. the columns ‘ScanID’, ‘Location’, ‘Latitude’, ‘Longitude’, ‘Automatic’. The combination of a consecutive series of ScanID’s and a value in the column ‘Location’ should result into […]

SUM of multi (relate to another table) rows record in another table

I have two tables an tblOrder and tblOrderDetail Table. tblOrderDetail contain below rows: OrderDetailID OrderID Product Quantity UnitPrice Discount Total 1 1 ABC 10 $240.00 10 $2,160.00 2 2 CDF 100 $200.00 10 $18,000.00 3 3 GHI 200 $150.00 0 $30,000.00 4 1 XYZ 40 $100.00 5 $3,800.00 i want sql query to get Subtotal […]

Generic script for finding orphaned data in Microsoft SQL Server tables

I have a Microsoft SQL Server 2012 database with a huge amount of tables (>100). I have also many many-to-many relation tables. All PK/FK relationships are made in the tables so that i know in which table which column belongs to which parent or child tables columns. I wanted to ask if it is possible […]

Duplication of data in SQL Query

I’m having an issue where there is data duplication on my query. This is how the issue looks like. I have a view table with data of: (PS_CX_FINC_SF_P_VW) ID | Item_Term | Item_Amt | Item_Type | Date | Item_NO —————————————————— 005976 | 1609 | 3000.00 | C | 2016-09-27 | 00010 (PS_CX_FINC_SF_A_VW) ID | Item_Term […]

SQL UPDATE table with OPENXML

I want to UPDATE a table within the following OPENXML statement but don’t know where to put the syntaxes as UPDATE, FROM and WHERE. I get a nice resultset with the OPENXML statement but how do I UPDATE a table with it? DECLARE @x xml SELECT @x=R FROM OPENROWSET (BULK ‘C:\Users\ronal\Dropbox\Projecten\Voormelden Portbase\20161021095457591.xml’, SINGLE_BLOB) AS XMLPortbase(R) […]

T-SQL Check Constraint based on a “hierarchy”

CREATE TABLE [dbo].[Test] ( [GID] INT , [ID] INT , [Val] VARCHAR(10) , [ParentId] INT , CONSTRAINT [PK_Test] PRIMARY KEY ( [GID], [ID] ) , CONSTRAINT [FK_Test_ParentId] FOREIGN KEY ( [GID], [ParentId] ) REFERENCES [dbo].[Test] ( [GID], [id] ) ); INSERT INTO [dbo].[Test] ([GID], [ID], [Val], [ParentId]) SELECT 1, 1,’One’,NULL UNION SELECT 1, 2,’Two’,NULL UNION […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.