How To Split Cells In A Table Format

Apr 5, 2008

Hi Guys,

I have this table formatting problem; I have a table report (with rows expanding) say sales report for items. In this report each item has 2 rows. I want to split the send row as shown below. So the results of the report should have the format below.






rownumber
ITEMNUNBER
COST
Sales
On Hand

1
A
CY4567
CY6780
CY567

2
A
LY4678
LM678
LY7999
LM789
LY500
LM10

3
B




4
B







5
C




6
C









IN this example for each item we show current year, last year and Last month Cost,sales and on hand.
This is not a cross tab and number of items onthe report varies. In this example its 3 items but next time when you run it could be 100 items.

Any help appreciated

Sonny

View 4 Replies


ADVERTISEMENT

Reporting Services :: How To Split Merged Cells

Oct 27, 2015

I am trying to split cells that are merged as part of a grouped report.  Am using the right-button menu and pressing "Split Cells" when it appears but it just splits into two parts and not into single cells or rows. When I right-button on one of the parts that came out of the split, I do not get a "Split Cells" to break it down further.  Want to be able to split the grouped cells into individual cells or rows and have the rows be different colors.  How can I do this?

View 4 Replies View Related

Change Or Check Format Of Excel's Cells In SSIS

Feb 10, 2008



I load data from excel and sometimes I have problem with format of excel's cells. For example format of cell should be "general" but one of cell is "custom" and I get wrong data but if I just change format in excel to "general" - data is correct.
Can I change or check format of cells in SSIS ?

View 1 Replies View Related

Updating Cells In A Table - Please Help

Jun 18, 2008

I have two questions. I am somewhat of a novice at this but would really appreciate some help.

Table = svc
There are multiple columns but I just need adjustments in the first 2.

Current Table:
code name svctype
CTS0003CT Abd Ltd 51608
CTS0005CT Abd W Cont 51608
CTS0011CT Abd WWO Cont 51608
CTS0013CT Abd WO Cont 51608
CTS0023CT Abd-Ltd Pel W Cont51608
CTS0025CT Abd-Ltd Pel WO Cont51608

What I want it update it to:
code name svctype
RCT0003AR CT Abd Ltd 19254
RCT0005AR CT Abd W Cont 19254
RCT0013AR CT Abd WO Cont 19254
RCT0011AR CT Abd WWO Cont 19254
RCT0023AR CT Abd-Ltd Pel W Cont19254
RCT0025AR CT Abd-Ltd Pel WO Cont19254

QUESTION #1:
So I am trying to figure out if I could write a statement that basically updates the CODE column’s first three letters in each cell from CTS to RCT and retains the numbers afterwards.


QUESTION #2
The NAME column in Table 1 would also need a little adjustment.
I need to add AR in front (almost like a prefix) of all of the descriptions (so that it looks like Table #2). How do I insert something into the description?

I greatly appreciate anyone's help in this. It would save me counltess hours.
-T.C.

View 2 Replies View Related

Table Cells...accessing A Value

Jan 11, 2007

hello All,

I am trying to now access the data (if any) in the current cell of a table. I want to know if this cell has been populated, and if so to move on...if not then I want to populate with a zero.



I am using the following:

ReportItems!textbox9.Value inside of an if statement but it tells me that I am unable to do so as, and I quote:

"The Value expression for the textbox 'textbox 9' contains a direct or indirect reference to itself. Loops in expressions are not allowed."



Any ideas guys?

View 5 Replies View Related

Formulae For SQL Server Table Cells

Aug 16, 2007

Hi,
I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:
I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.
Heres an example:
TABLE(A, B, C, D)
Total Records = 9;




A
B
C
D

A1
B1
C1 = A1+ B1
D1 = A1

A2
B2
C2 = A2+ B2
D2 = A2

A3
B3
C3 = A3+ B3
D3 = A3

A4
B4
C4 = A4+ B4
D4 = A4

A5
B5
C5 = A5+ B5
D5 = A5 + B5

A6
B6
C6 = A6+ B6
D6 = A6 + B6

A7
B7
C7 = A7+ B7
D7 = A7 + B7

A8
B8
C8 = A8+ B8
D8 = A8 + B8

A9
B9
C9 = A9+ B9
D9 = A9 + B9
If you notice, for Column D, some of the cells have a different formula than others. Is this doable?
Thanks...

View 5 Replies View Related

Subreports Within Table/matrix Cells Are Ignored.

Apr 25, 2007

I have Report with subReport,

in the designer its looked well, but when I export the report to excell format

I get this error,

is it poosible export report with sub report to excell?

thanks!

View 33 Replies View Related

Replace Characters On Condition In Table And Cells

Apr 14, 2008

need help

replace characters on condition in table and cells

but only if

if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)

i replace characters with '0' and '#'

and it must work dynamically AND replace ONLY THIS characters


table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B

table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #

tnx FOR THE HELP

View 5 Replies View Related

Data Regions Within Table/matrix Cells Are Ignored

Dec 7, 2007

My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated

Regards
Praveen John
+91-9895074288
"Frankly, my dear, I don’t give a damn"

View 1 Replies View Related

Data Regions Within Table/matrix Cells Are Ignored

Dec 7, 2007

My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated

View 1 Replies View Related

Autofit Column Width In Table Cells In Rdl Files

Dec 18, 2007

Hi,

I am facing issue with the auto fit width. When i am creating a report which includes table. The table column length should get adjusted to the text size displayed in it instead of displaying the text in 2 lines. But i dont find any way to set that option. Could anyone let me know how to set the column length as per the text displayed in the column in table.

Thanks

View 11 Replies View Related

Error Data Regions Within Table/matrix Cells Are Ignored

Mar 13, 2008



When we try to export to excel a SQL client report containing tables that were grouped based on some data in two tables of a dataset I am receiving the following error

"Data Regions within table/matrix cells are ignored".

View 8 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Transact SQL :: How To Format A String In A Format Coming From A Table

Jun 4, 2015

I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?

View 18 Replies View Related

Could You Tell What's Wrong When I Split Table To The Target Partition Table?

Jan 22, 2007

Could you tell what's wrong when I split table to the target partition table?USE TEST--ADD FILEGROUP---------------------------------------------------------------------ALTER DATABASE TEST ADD FILEGROUP FG_01ALTER DATABASE TEST ADD FILEGROUP FG_02ALTER DATABASE TEST ADD FILEGROUP FG_03--ADD FILE--------------------------------------------------------------------------ALTER DATABASE TEST ADD FILE (NAME = DF_01,FILENAME = 'D:TESTDF_01.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_01ALTER DATABASE TEST ADD FILE (NAME = DF_02,FILENAME = 'D:TESTDF_02.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_02ALTER DATABASE TEST ADD FILE (NAME = DF_03,FILENAME = 'D:TESTDF_03.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_03--CREATE PARTITION FUNCTION---------------------------------------------------------CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)AS RANGE LEFT FOR VALUES ('20070101 23:59:59.997','20070102 23:59:59.997')--CREATE PARTITION SCHEME-----------------------------------------------------------CREATE PARTITION SCHEME PS_HIS_HTTP_LOGAS PARTITION PF_HIS_HTTP_LOG TO ( FG_01, FG_02, [PRIMARY])--CREATE PARTITION TABLE -----------------------------------------------------------CREATE TABLE HIS_HTTP_LOG ( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON PS_HIS_HTTP_LOG(STARTIME)--INSERT DATA,PARTITION 1 20070101-------------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 100BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())SET @i = @i +1END--INSERT DATA ,PARTITION 2 20070102-------------------------------------------------SET @i = 1WHILE @i <= 200BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())SET @i = @i +1END--CREATE A TABLE -------------------------------------------------------------------CREATE TABLE TMP_HTTP_LOG( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON FG_03--INSERT DATA TO TMP_HTTP_LOG 20070103-----------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 400BEGININSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())SET @i = @i +1END--ADD CONSTRAINT--------------------------------------------------------------------ALTER TABLE TMP_HTTP_LOGWITH CHECKADD CONSTRAINT CK001CHECK (STARTIME >= '20070103 00:00:00.000' AND STARTIME <= '20070103 23:59:59.997')--SPLIT RANGE ,SWITCH DATA----------------------------------------------------------ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3--==========================================�======================================Why is the error in step of“ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3�error infomation:message_id 4972,level 16,severity 1ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.Please tell me why ? check constraints ?Thank you very much !

View 1 Replies View Related

Smarter Table Build - Might Split The Table

Jul 10, 2006

Hello,I am using SQL 2005 and Cognos' Data Manager. It is an ETL tool fordata warehousing.I have a problem with time it takes to load new changes, and I amseeking advice on a better way to manage the data.I have a table that tracks student attendance and it contains about 13million records. On a daily basis, there are 5,000 - 20,000 inserts and10,000 - 50,000 updates.The daily data comes for two different text files from my operationsystem; current and historical (CLSFIL and CLSHIS).The data is loaded into a staging area from the operational system,where data cleansing and other fields are added to the table.The final step is delivering the table to my target database, which isused for reporting.Heres the situation: I find it takes 45 minutes to do a relationalupdate, where only the records that changed in the last day will beloaded. However, if I choose the native API load instead of aRelational Load, it can load all 13M records in 7 minutes. The table isheavly indexedAt some point, the API load will take more time than the relationalload, (the changes and new records will remain a constant, but the filewill continue to grow).I'm seeking another solution is more efficient. I'm considering twotables for history and current and creating a view for reporting via aunion.This a good idea? How can I make the view effeicent to use the whereclause? Looking to bounce around ideas.Other Ideas?Thanks in AdvanceRob(I maintain the key relationships in the tool, not the tables. I knowI have lots to learn and improvments)CREATE TABLE "dbo"."F_BI_Class_Attendance_Detail"("CLASS_ATTENDANCE_ID" VARCHAR(50) NULL,"CLASSES_OFFERED_ID" VARCHAR(26) NULL,"CLASS_CAMPUS_ID" VARCHAR(10) NULL,"STUDENT_ID" CHAR(20) NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"SESSION_ID" CHAR(10) NULL,"SECTION_ID" VARCHAR(5) NULL,"MEET_DT" DATETIME NULL,"MEETING" SMALLINT NULL,"PRESENT" CHAR(2) NOT NULL,"SESSION_SKEY" BIGINT NULL,"STUDENT_SKEY" BIGINT NULL,"CLASS_CAMPUS_SKEY" BIGINT NULL,"CLASSES_OFFERED_SKEY" BIGINT NULL,"LOAD_DT" DATETIME NULL,"COMPUTED_DT" DATETIME NULL);

View 3 Replies View Related

SQL Server 2008 :: Updating Server Table From Excel Cells?

Jul 3, 2015

I have an sql server table which serves as a criteria table for my sql server query.

i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.

The table in question has the following fields

SELECT
[Cluster]
,[Max_Break_btw]
,[RefD_Max_Break]
,[DischD_Max_Break]
,[MaxReviewPeriods]
FROM [databseName].[dbo].[SpellClusterAssum]

I will like to change / update the values in the "[Max_Break_btw]" column.

View 0 Replies View Related

Is There A Way To Do Mass Formatting Of Cells In A Table Instead Of Cell By Cell?

Oct 5, 2007



Hi,
I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.

I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?

Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.

Any help would be much appreciated. Thanks!

View 4 Replies View Related

Big Table(?) Or Split Between Tables?

Nov 20, 2007

Hi Guys

I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars.

I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba).
The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way?

The questions are:

Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on?
Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on.

How many rows is okay to have in one table (with 25 columns) before its too big?

Thanks in advance.

Best regards
Johan, Sweden.

CREATE TABLE [dbo].[Cdr](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Abandon] [varchar](7) NULL,
[Bcap] [varchar](2) NULL,
[BlId] [varchar](16) NULL,
[CallChg] [varchar](6) NULL,
[CallIdentifier] [uniqueidentifier] NULL,
[ChgInfo] [varchar](5) NULL,
[ClId] [varchar](16) NULL,
[CustNo] [smallint] NULL,
[Digits] [varchar](32) NULL,
[DigitType] [varchar](1) NULL,
[Dnis1] [varchar](6) NULL,
[Dnis2] [varchar](6) NULL,
[Duration] [int] NULL,
[FgDani] [varchar](13) NULL,
[HoundredHourDuration] [varchar](3) NULL,
[Name] [varchar](40) NULL,
[NameId] [int] NOT NULL,
[Npi] [varchar](2) NULL,
[OrigAuxId] [varchar](11) NULL,
[OrigId] [varchar](7) NULL,
[OrigMin] [varchar](16) NULL,
[Origten0] [varchar](3) NULL,
[RecNo] [int] NULL,
[RecType] [varchar](1) NOT NULL,
[Redir] [varchar](1) NULL,
[TerId] [varchar](7) NOT NULL,
[TermAuxId] [varchar](11) NULL,
[TermMin] [varchar](16) NULL,
[Termten0] [varchar](3) NULL,
[Timestamp] [datetime] NOT NULL,
[Ton] [varchar](1) NULL,
[Tta] [int] NULL,
[Twt] [int] NULL,
[DateValue] [int] NULL,
[TimeValue] [int] NULL,
[Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'),
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

View 14 Replies View Related

Get Split Names From Id Of Other Table

Jul 10, 2006

Table :: master_Staff

StaffID -Number
StaffName-String




Table :: master_Class

ClassID-Number
ClassTeacher-Number ( Forign Key of StaffID )




I have a set of StaffID saved using "," in the table class -------- 10,20,30

Now i want a Query to get the staff name -- hari, gopal, sekar , by using the classID

ie::
select staffname from master_staff where staffid in ( select classteacher from master_class where classID = 1)

View 9 Replies View Related

SQL Optimistic (all In One Table Split Into Two Table)

Dec 18, 2007

I having a bit of confuse here. Can you please help me?

I have about 5000 records all ready in oen table. Everything that I query is related to that table one way or the other. Now i having 2000 - 3000 more records to store in the database. In term of relation database then I can store the new data in a different table so I can can query it. Most of my queries are searching.

So the question is is this better to store the data in another table or should store everything in the old table? Thanks a lot in advance for your help. I really do appreciate that.

Maverick

View 2 Replies View Related

T-SQL To Split Data From One Table Into Two Tables?

Feb 21, 2005

What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.


-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)

-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)

View 5 Replies View Related

Importing Split Data Into Table

Apr 4, 2006

Hi,

I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table.
I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table.

Thanks

Guy

View 6 Replies View Related

Should I Split This 175 Million Record Table?

Jul 20, 2005

Hello,We maintain a 175 million record database table for our customer.This is an extract of some data collected for them by a third partyvendor, who sends us regular updates to that data (monthly).The original data for the table came in the form of a single, largetext file, which we imported.This table contains name and address information on potentialcustomers.It is a maintenance nightmare for us, as prior to this the largesttable we maintained was about 10 million records, with lesscomplicated updates required.Here is the problem:* In order to do the searching we need to do on the table it has 8 ofits 20 columns indexed.* It takes hours and hours to do anything to the table.* I'd like to cut down as much as possible the time required to updatethe file.We receive monthly one file containing 10 million records that arenew, and can just be appended to the table (no problem, simple importinto SQL Server).We also receive monthly one file containing 10 million records thatare updates of information in the table. This is the tricky one. Theonly way to uniquely pair up a record in the update file with a recordin the full database table is by a combination of individual_id, zip,and zip_plus4.There can be multiple records in the database for any givenindividual, because that individual could have a history that includesmultiple addresses.How would you recommend handling this update? So far I have mostlytried a number of execution plans involving deleting out the recordsin the table that match those in the text file, so I can then importthe text file, but the best of those plans takes well over 6 hours torun.My latest thought: Would it help in any way to partition the tableinto a number of smaller tables, with a view used to reference them?We have no performance issues querying the table, but I need somethoughts on how to better maintain it.One more thing, we do have 2 copies of the table on the server at alltimes so that one can be actively used in production while we runupdates on the other one, so I can certainly try out some suggestionsover the next week.Regards,Warren WrightDallas

View 7 Replies View Related

Read The Filename, Split It And Put It In A Table

Sep 21, 2006

Hello

I'm working on a package which loops through each xml file in a folder.
The name of each xml file is put in variable.
The format of the filename is something like "part1_part2_part3.xml"
I need to store the 3 parts in three different columns of table A
The content of the xml file needs to be manipulated ("." needs to be replaced with ",", ....)and put in serveral columns in tableB

It's not clear to me yet how to start this but my main concern is read the three parts of the filename. I don't find any task in SSIS which could help me with that.

Could someone give me some pointers?

Many thanks!

Worf

View 3 Replies View Related

Split Data Into Two Column Table

Mar 13, 2007

Hello all,

Little layout question. Assume my dataset returns the following data:

A

B

C

D

E



How can I present this data in a table (or list, or matrix) splitted into two columns:

A B

C D

E



Any idea will be very appreciated! Thanks a lot!

TG

View 4 Replies View Related

How To Split Out Table Rows Into 3 Tables

Mar 14, 2006

I imported all rows of my txt file using SSIS 2005 into a table.  I am now trying to figure out how to split out the header, payment rows, and maintenance rows.  First, some information.

An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row

The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).

I need to

1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table

I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.

To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31.  If it contains 'MT' then you know it's a maintenance row, else it's a payment row.

How in the hell do I do this???

View 4 Replies View Related

Please - Split Select From Table To Comma 1,2,3,...

May 29, 2008

help please
i have this table

number_row fld_index vtext
----------------------------------------
1 101 a
2 101 b
3 101 c
4 102 d
5 102 e
6 102 f
7 103 g
8 103 h
9 103 i
......
......
....
now i need to do this

SELECT fld_index
FROM table_index
GROUP BY fld_index

----------------------------
and i get

101
102
103
104
how split this select and do this

in('101' ,'102','103','104')
------------------------------



Code Snippet

DECLARE @aaa

set @aaa =(SELECT fld_index FROM table_index GROUP BY fld_index)

set fld1 = CASE WHEN fld1 in(@aaa ) then '*' else fld1 end ---- need to split the @aaa ,'101' ,'102' , '103','104' ,





----------------------------------------------------------------------------------------------------------------------------
instead of this update



update [dbo].[tb_pivot_big]

set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end

, fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end

, fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end



, fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end



, fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end





from [dbo].[tb_pivot_big]

View 23 Replies View Related

Measure Page Split Online For A Particular Table?

Jan 21, 2003

Is there a way to measure page split for a table during data insertion/update (not base on dbcc showcontig result)?

thanks...byyu

View 3 Replies View Related

SQL View To Split Rows In Single Table...

May 24, 2007

I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for

I have a table is MSSQL 2000 that looks as follows:


Code:



id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1



Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:


Code:



custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1



Any help would be great! I imagine some sort of join command can be constructed using a single table?

View 2 Replies View Related

Split String And Insert Into Table Rows

Apr 17, 2014

I have a string which is delimited by commas:

Green,Red,Blue,Orange,Black,White

I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).

For instance:

INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...

I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.

View 4 Replies View Related

Comparing A Column List Split To A Table.

Jul 23, 2005

Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo

View 3 Replies View Related

Load The Conditional Split Conditions From A Table

Apr 23, 2007

Hi everybody,



I want to load the conditions for a conditional split from a table, so that the bussiness logic can be changed with a simple user interface .



How can I do this?





Best regards



John

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved