SQL Server 2012 :: Normalizing A Column Containing Lists

Aug 20, 2015

CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))

INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')

The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.

The results I need are:

CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40

I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?

View 1 Replies

SQL Server 2012 :: Formatting XML Output - Avoid Normalizing Structure On Client

May 28, 2015

I have a script that resolve's data into xml like this, ex:

<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>

And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.

For the root I am using

FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS

View 0 Replies View Related

Need Help Normalizing Multivalue Column

Jan 23, 2006

Hi!

I have a table with the following columns:

account_nr, account_totaling_members, account_type

the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"

I need to normalize this so that i get records like:

"10", "1001", "sum"

"10", "1001", "sum"

"10", "1002", "sum"

..and so forth

Does anyone have any idea how to accomplish this?

View 3 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

SQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?

Sep 18, 2014

A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.

I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.

The text before ;# (35106) should be inserted in to another column of the same table.

Below is the table structure:

create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)

View 2 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Update A Column Using Value Of Another Column

Sep 9, 2015

I have a student table like this studentid, schoolID, previousschoolid, gradelevel.

I would like to load this table every day from student system.

During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.

My question in my merge statement something like below

Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid

When matched then update

set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;

My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?

View 4 Replies View Related

SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)

Aug 21, 2015

I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.

I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.

There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.

Special Characters are defined as #%&*:<>?/{}|~ and ..

While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.

Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16

The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.

In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.

My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.

I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.

View 3 Replies View Related

SQL Server 2008 :: Find Records Comparing Two Lists

Jul 31, 2015

Below is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.

create table dbo.PatientStatus
as
(
patient_id varchar(10),
status_type varchar(10),
status_date datetime

[Code] ....

View 2 Replies View Related

SQL 2012 :: Why Does Server Consider A Value To Be A Column Name

Jul 22, 2015

This should be a really simple, straight forward query, but apparently it's not. The error I get is "Invalid column name 'A'. I've put it in quotes and not in quotes and I left spaces around the equal sign and I've eliminated them. What's wrong with the SELECT statement below?

SELECT * from firms WHERE firm_id= 100002
AND active_firm = 1 AND firm_id != 100092
AND trust_id = A;

View 6 Replies View Related

Need To Schedule A Web Service To Truncate And Populate SharePoint Lists (2007 && 2003) From A SQL Server 2005 Table

Jun 24, 2007

I couldn't find any tutorials/articles on integrating a SharePoint web service call with an SSIS package

I need to schedule a web service to truncate and populate SharePoint Lists (2007 & 2003) from a SQL Server 2005 table.

I guess I'm just ignorant about integrating web services with SSIS. How do I make a call to the web service through my SSIS package? (script task?)

Any links to articles/tutorials or pointers would be apreciated.

View 3 Replies View Related

SQL Server 2012 :: Need To Select A Column Without 0

Jun 21, 2014

I have a table like below

create table #temp
(
valid_id int not null,
valid_no varchar(50)
)

10001
20002
30011
40012

i need to select the valid no without '0' lie 1,2,11,12, how do i?

View 7 Replies View Related

SQL Server 2012 :: Get Row Value As Column Header

Dec 21, 2014

I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.

UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL

The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.

UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL

I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.

UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8

17211
27227
37289
47268
57278
67307
77214

I have attached the screenshot of the data for reference. I am not that much aware of sql queries.

[URL] .....

View 9 Replies View Related

SQL Server 2012 :: Select Value Then Must Put In Column

May 6, 2015

I am trying to select leveling; I have table like this;

FACC_WID FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE

[Code] ...

Now I need to make 5 levels like this:
1
10
100
1001
100100

When column FACC_CODE have 1 char then its a level one so I put this in FACC_H1_L1_CODE..

I this possible in (SSIS)?

View 1 Replies View Related

SQL Server 2012 :: How To Get A Sum Of One Set Of Data In Same Column

Sep 6, 2015

I put on the site excell file where I explained what I want to get . It is easier to explain through an example .

THIS IS MY INPUT DATA:

ACCOUNTS CODE ACCOUNTS NAME TOTAL

0 CLASS 0
011 Synthetic group
011-1301 Some name 1 25 $
011-1401 Some name 2 20 $
022 Synthetic group
022-1011 Some name 1 101 $

[code]...

View 9 Replies View Related

SQL Server 2012 :: DateAdd From Max DateValue Column

Jan 8, 2014

I am trying to generate a @StartDate and @EndDate. The @StartDate would be the 1st of January and the year is dependent on the @EndDate.

The @EndDate would be
select max(DateValue) from dbo.testtableThe column DateValue in the testtable contains dates such as 2013-12-09, 2013-12-15.

What I am trying to accomplish is if the @EndDate is populated by select max(DateValue) from dbo.testtable returns '2013-12-15' as the max(DateValue). I want the @StartDate to '2013-01-01' because the year of the EndDate is 2013.

So something along the lines of
@StartDate = DateAdd(yy,?,@EndDate)

View 2 Replies View Related

SQL Server 2012 :: Group By Column As Sub Header

Jan 28, 2014

I have a table which I would like to format like so:

From this:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED

[Code] ....

Just as a single varchar(max) field.

View 9 Replies View Related

SQL Server 2012 :: More Than 8000 Chars In A Column

Jan 30, 2014

Is there any possibility to store more then 8000 chars in a column?

View 1 Replies View Related

SQL Server 2012 :: How To Get Column Used In Where Condition In A Procedure

Apr 16, 2014

Is there any way to get the Columns Used in where condition in a procedure using SQL?

View 1 Replies View Related

SQL Server 2012 :: How To Check Which Row / Column Truncated

May 16, 2014

Table Temporary (Staging) - 400.000 rows and 200 columns.

When i done insert from temporary table to target, i have this:

Msg 8152, Level 16, State 13, Line 9

String or binary data would be truncated.

The statement has been terminated.

How can i check which row/column truncated?

View 9 Replies View Related

SQL Server 2012 :: How To Update One Column Value To Another In Same Table

Jun 21, 2014

my table payment_details structure is

payment_id payment_code
1 null
2 null
3 null
4 null

here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query

update payment_details
set payment_code = payment_no
where payment_code is null

but it shows subquery error?

View 3 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL 2012 :: Aliasing Column Names In Server

Jan 6, 2015

Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.

View 2 Replies View Related

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

SQL Server 2012 :: Compare Column In Two Tables?

Apr 20, 2015

i have two tables.

Table A
IdName
101Dante
102Henry
103Harold
104Arnold

Table B
NumberName
102Dante
107Gilbert
109Harold
110Arnold
106Susan
112Marian

I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.

Table C
Col1Col2
HenryTable A
Gilbert Table B
Susan Table B
Marian Table B

using below logic to get the values from tables..

select
t1.columnA
, t2.*
from
table1 t1
join table2 t2 on t2.columnB = t1.columnA

View 9 Replies View Related

SQL Server 2012 :: Get Splitted Column Using Function

Apr 21, 2015

I am having staging table with separted by '¯'.I want to split the data with given number .i have given 31 means my main table have 31 column. it should handle the less or more column.

declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'

[code]....

View 9 Replies View Related

SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies View Related

Normalizing My Database

Nov 8, 2006

Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help

View 6 Replies View Related

I Need Help Normalizing This Table.

Aug 10, 2005

So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?Here's the table: and here's the site that uses this table:http://waahp.byu.edu/links.aspThanks!~Cattrah~

View 3 Replies View Related

Normalizing Using Sql Commands

Aug 28, 2006

Hi

Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?

I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.

thanks in advance

View 11 Replies View Related

Primary Key, Normalizing?

Feb 24, 2004

View 6 Replies View Related

De-normalizing Query

Jul 13, 2006

I have this table...CREATE TABLE #Test (ID char(1), Seq int, Ch char(1))INSERT #Test SELECT 'A',1,'A'INSERT #Test SELECT 'A',2,'B'INSERT #Test SELECT 'A',3,'C'INSERT #Test SELECT 'B',1,'D'INSERT #Test SELECT 'B',2,'E'INSERT #Test SELECT 'B',3,'F'INSERT #Test SELECT 'B',4,'G'....and am searching for this query....SELECT ID, Pattern=...?? FROM #Test....??....to give this result, where Pattern is the ordered concatenation ofCh for each ID:ID PatternA ABCB DEFGThanks for any help!Jim

View 2 Replies View Related

Normalizing A Crosstab

Aug 24, 2006

I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]

View 5 Replies View Related