Link ID to same User

Hello I am using SQL SERVER 2008R2 and have tables which produces following output, **Usertable** USERID Name LINKID 1 abc NULL 2 pqr NULL 3 str NULL 4 xyz NULL **PhoneTable** USERID PhoneNumber 1 123 2 123 3 12345 4 34567 **EmailTable** USERID EmailID 1 abc@gmail.com 2 pqr@gmail.com 3 abc@gmail.com 4 xyz@gmail.com My requiremnet is, […]

Tsql add column using synonyms

I have to add two columns to a table and I have a synonym associated with that table For example: My table name is table_abc and the synonyms is table_1 When i try to add column to the table in normal way, ALTER TABLE [dbo].[table_1] ADD test_1 varbinary(MAX), test_2 varbinary(MAX); Following error occurs : Cannot […]

SQL Server change collation on varchar columns that has a foreign key constraint

Is there any easier way to change the collation on columns with foreign key constraints than manually removing them and adding them back up? I have tried the disabling of the constraints without luck with the statement: ALTER TABLE PM_Minnekorter NOCHECK CONSTRAINT ALL Is it possible to automate the dropping of the constraints and recreation […]

SQL Pivot possible for this data set?

I trying to write a query and I need some help. I have some data that looks like this: However, the end users would like a Pivot so that the data displays like this: I wrote this code: SELECT * FROM ( SELECT TOP 100 Log0.sn ,CONVERT(VARCHAR(50), Log0.capture_time) AS Instant ,CONVERT(VARCHAR(50),Log0.address) + ‘/’ + Log0.[Key] […]

Select Min And Max Value from one table with same id in one row (sql server)

I have table like this: | ID | Date | |—-|———————| | 20 | 2017-03-01 08:00:00 | | 20 | 2017-03-01 17:00:00 | | 21 | 2017-03-01 07:00:00 | | 21 | 2017-03-01 17:10:00 | How can I select that to show result like this: | ID | Date | IN | OUT | |—-|———————|——————–|——————–| […]

MS SQL Insert into concat

I need pick up first character from Column2_table1 and add it before value from Column1_table1 and this join value i would like get into emptly column Column3_table1. I have : Column1_table1 Column2_table1 Column3_table1 1234 abcd 1245 aeio 1545 dfaf 1545 jhui INSERT INTO [dbo].[Table1] ([Column3_table1]) SELECT Column1_table1, CONCAT(left(Column2_table1,1) ,Column1_table1) FROM Table1 Msg 121, Level 15, […]

SQL Server – Get Parent values from Child recursively

So I’ve been struggling with this for the past hour or so now I’m writing a script to extract some data in a nice format for me to use in another process but I can’t quite figure out this particular bit (I don’t use SQL Server much) I’ve got several tables that are all involved […]

Executing an SQL command with EntityManager

Is it possible to run an SQL script that doesn’t return a resultSet or is not an INSERT or UPDATE type of script? I’m trying the following: Query q = entityManager.createNativeQuery(“DECLARE @max int;\n” + “SELECT @max = MAX(customer_number)+1\n” + “FROM organisation\n” + “\n” + “exec(‘ALTER SEQUENCE organisation_customer_number_seq RESTART WITH ‘ + @max)”); …but of course […]

Multiple rows, with multiple values, how to get higher value for same row ID

I’m having data contained in my SQL database, its about students and their cards which they are using to log in to system, sometimes students lose their card and they get another one, but same row which contains informations about student card was not updated, in fact new row was added, so we are getting […]

How to write update query using Inner join

I have this query Update ProductionDetails P Inner join ( SELECT Distinct A.BaseCode, A.BaseScale, (A.BaseScale * B.BasePer / 100) AS BaseVal, A.TreadCode, A.TreadScale, (A.TreadScale * B.TreadPer / 100) as TreadVal, A.InterfaceCode, A.LipCode, A.LipScale, (A.LipScale * B.HsPer / 100) as LipVal, A.CenterCode, A.CenterScale, (A.CenterScale * B.CenterPer / 100) AS CenterVal, A.StencilNo from productionDetails A inner join […]

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