Transact SQL :: How To Break Rows Into Multiple Columns

Jul 14, 2015

I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.

Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100

Output will be like
c1  c2   c3    c4    c5....
1    5    9      13
2    6    10    14
3    7    11    15
4    8    12    16

View 4 Replies


ADVERTISEMENT

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

SQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?

Jul 1, 2015

Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.

Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not

CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment

I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.

View 2 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

Merge Multiple Rows Into A One Or More Rows With Multiple Columns

May 7, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

ID Pat_ID

1 A


2 A
3 A
4 A
5 A
6 B

7 B
8 B
9 C

10 D

11 D




I want output for the above as:

Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null

Please help me. Thanks!

View 6 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008



Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Apr 23, 2008



Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which


So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!


View 3 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Parent Table - (multiple) Rows Into (multiple) Columns

Feb 12, 2015

I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:

I need results for each parent like this

ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]

View 1 Replies View Related

Processing Multiple Rows And Multiple Columns In SSIS

Sep 26, 2007

I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!

View 1 Replies View Related

Transact SQL :: Convert Rows Into Columns?

Oct 8, 2015

How can I convert the following data

Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)

[Code] ...

into the following format

Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),

[Code] ....

I am looking at this [URL]

how to do this in SQL 2000.

View 16 Replies View Related

Transact SQL :: Convert Rows To Columns?

Apr 24, 2015

i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .

create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)

[code]...

View 9 Replies View Related

Transact SQL :: Query To Make Rows To Columns

May 18, 2015

I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)

name language
-------------
ron   english
ron   french
ron   spanish
andy  english
andy  hindi
kate  english

Desired output

name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron  english   french    spanish
andy english   hindi
Kate english 

View 4 Replies View Related

Transact SQL :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payer varchar (5),                  
ID varchar (5),      
ClM varchar (2),                 
Paid1 float,
Paid2 float,
Paid3 float
)                      

[code]....

Desired result:

Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
 001 002
76.58 19.53
 153.48 96.43
 53.48   200

View 10 Replies View Related

Transact SQL :: Pivot Columns Into Rows With Difference

Sep 22, 2015

Here is the table:

empid lastname firstname title titleofcourtesy ModifiedOROriginal

1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original

We need an output like this:

empid
1
1

lastname
Davis1
Davis

firstname
Sara
Sara

title
CEO
CEO

titleofcourtesy
Ms.
Ms.

ModifiedOROriginal
Modified
Original

View 2 Replies View Related

Multiple Rows And Columns MAX

Jul 3, 2006

Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.

View 3 Replies View Related

Transact SQL :: Turn Rows Into Named Columns Using CASE

Nov 19, 2015

That may not be the most exact TITLE I have used for a question as CASE may not be the best expression to use but I have a query:

select AR.ClientID,
AR.ReferenceID,
AAC.SequenceID,
AA.AppearanceDate,
CASE
WHEN AAC.Outcome IS NULL Then 'Cancelled' else

[Code]....

This returns the following data set:

ClientID ReferenceID
SequenceID AppeareanceDate
Outcome Rownum

1000000 2
2000358 2005-11-07
Yes 1

[Code] ...

What I want the eventually solution to be is that for each unique/distinct client, reference and sequence there is one record (row). Based on the row_number, I want the first 'AppearanceDate' renamed to 'AppearanceDate1' with the first AppearanceDate as the data and the first Outcome to be renamed 'Outcome1' with the first Outcome as the data. Any subsequent rows would follow as columns:

ClientID ReferenceID
SequenceID AppeareanceDate1
Outcome1  AppearanceDate2
Outcome2
1000000 2
2000358 2005-11-07 
Yes NULL

[Code] ...

Only four AppearanceDates and outcomes are required based on the unique/distinct Client, Reference and SequenceIDs. How I can loop through the Appearances based on the row_number and show each record in one row?

View 3 Replies View Related

Transact SQL :: Keep Entire Columns Into 2 Rows When There Is A Comma In A Column

Nov 2, 2015

I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.

ID       Name                  State      Country
1       Scott, Ricky            NSW       AUS
2       Martin                   VIC         AUS
3    James, Peter,John     WA        AUS

ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below

ID       Name                  State      Country
1       Scott                     NSW       AUS
1       Ricky                     NSW       AUS
2       Martin                   VIC         AUS
3      James                     WA        AUS
3      Peter                      WA        AUS
3      John                       WA        AUS

How to strip the camma(,) in SQL?

View 3 Replies View Related

Transact SQL :: How To Convert Table Columns Into Rows In 2005

Aug 27, 2015

i have following table columns and i want to convert these all columns into row

SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]

[Code] ....

required output looks like

CASHINHAND 118950
CASHATBANK  200
DEPOSITS 3000
ACCRECEIVABLE
25000

View 3 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

View 15 Replies View Related

Transact SQL :: COUNT And SUM Of Multiple Columns

Sep 2, 2015

I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.

CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]....

View 8 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

Multiple Rows To One Row And Multiple Columns

Sep 7, 2007



Hi,
I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work.
Heres what i want to do:


This is the how the table is:
EmpID Designation




678
CFA

679
CFA

680
CFA

685
CFP

685
CIMA

685
IMCA


I want it to display as:
EmpID Designation1 Designation2 Designation3
678 CFA
679 CFA
680 CFA
685 CFP CIMA IMCA

could anyone provide some help on this?

Thanks

View 1 Replies View Related

Transact SQL :: SUM Of Multiple Rows With Where And Group By

Apr 30, 2015

I have a table Transaction that looks something like the following :

TransactionID          

Currency        Credit             Debit
1     USD      500               0
2     Afcu          6000              0

[Code] ....

I write query like this

select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source

And it came like

Balance Source           
1500                        USD
6000                        Afcu
6800                        INR
7000                        Pfc
-200                         AUD

But I also want to add  Afcu , Pfc with USD and  want output like

Balance Source
14500                        USD
6800                          INR
-200                           AUD

View 3 Replies View Related

SQL Server - Multiple Rows Into Columns

Jan 27, 2004

Hello!

I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures?

Please let me know.

Thanks much.

-PV

View 5 Replies View Related

How To Split Columns Into Multiple Rows

Jun 7, 2008

SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor

Destination Table

ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor


Please someone help me on this.

View 4 Replies View Related

Summing Multiple Rows In Columns

Jul 9, 2014

I am working with a MS SQL database associated with SCCM 2007. SCCM collects software product usage data, and I am tasked with generating a report that will return results between two user-chosen date ranges. I set up prompts for month, year, endmonth, endyear. They would enter 2, 2014, 4, 2014 for example. Timekey would equal 201402, endtimekey = 201404. The tables I am concerned about look like this:

v_MonthlyUsageSummary

TimeKey ResourceID FileID UserID UsageCount TSUsageCount UsageTime LastUsage
201402108343 253362240038122 0 2550272014-02-28 13:29:39.000
201402108340 253362240665 2 0 122014-02-27 15:58:13.000
201402108932 253362231708 0 10 216702014-02-14 20:41:35.000
201403124035 504006542145 15 0 1756292014-03-31 19:37:37.000
201403124036 504006537579 46 0 7737112014-04-01 00:00:00.000

v_SummarizationInterval

TimeKey IntervalStart
2013102013-10-01 00:00:00.000
2013112013-11-01 00:00:00.000
2013122013-12-01 00:00:00.000
2014012014-01-01 00:00:00.000
2014022014-02-01 00:00:00.000
2014032014-03-01 00:00:00.000
2014042014-04-01 00:00:00.000
2014052014-05-01 00:00:00.000
2014062014-06-01 00:00:00.000
2014072014-07-01 00:00:00.000

The problem is I only know how to grab one timekey, but I need to combine multiple timekeys to sum up the usage counts for each workstation.

Here is the full query.

declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=100*@Year+@Month
set @endTimeKey=100*@endYear+@EndMonth
select @months=DATEDIFF(d,@timekey,@endTimeKey)

[Code] .....

View 20 Replies View Related

Transact SQL :: Convert Unknown Number Of Questions From Rows Into Columns

Jun 17, 2015

Using the following tables and data---

CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')

[Code] ...

And currently outputs;

Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015

How complex is the structure?

Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million

Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)

[Code] ....

However, I would like it to output;

Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?

[Code] ....

The number of questions are unknown for each RiskReviewID and they can be added to in the future.

View 7 Replies View Related







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