473,882 Members | 1,575 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

converting rows to columns

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out
PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZ
Jul 20 '05 #1
7 23505
Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5) , a.begin_time, 108) AS begin_time,
CONVERT(char(5) , a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pi eces), 0) AS PopCorn,
ISNULL(SUM(c.Pi eces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ
Jul 20 '05 #2
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ
Jul 20 '05 #3
"John Bell" <jb************ @hotmail.com> wrote in message news:<bt******* ***@titan.btint ernet.com>...
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ


Another solution would be using a union statement

select * from departinfo where popcorn = 0
union select * from departinfo where bisuits = 0
order by begin_time
Jul 20 '05 #4
Hello Dan,
Thanks for the quick response !
The solution you have provided wil not solve my problem due to
following reason :

The departinfo table records are not fixed as shown below. The time
difference can vary also the remainig data. The table will be
populated in the following format. Only the format is fixed the
records are not.

begin_datetime end_datetime Name Pieces

So my requirement is to convert the Name field into unique distinct
columns and match the pieces.
for example if the Name field contains items like Biscuits, Pizza,
PopCorn
then these many columns should be created and matched with the pieces.
Please help !
Thanks
Pooj


"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message news:<8H******* *******@newsrea d1.news.atl.ear thlink.net>...
Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5) , a.begin_time, 108) AS begin_time,
CONVERT(char(5) , a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pi eces), 0) AS PopCorn,
ISNULL(SUM(c.Pi eces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

Jul 20 '05 #5
Hi John,

Thanks for the quick reply.I checked the link u have provided but it
seems it
won't solve my problem b'coz DaypartInfo table records are not fixed.
They can vary E.g.my table format is as below:
begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

In this table I want to convert the Nae field records as columns i.e.
Name field may contain PoCorn ,Pizza ,Biscuits,Chees e etc . Basically
these records can vary and those should be converted as columns at
runtime...

Please help....

Thanks,
Pooj.


"John Bell" <jb************ @hotmail.com> wrote in message news:<bt******* ***@titan.btint ernet.com>...
Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...
have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

Jul 20 '05 #6
Hi

This may help...

http://www.sqlteam.com/item.asp?ItemID=2955

John
Jul 20 '05 #7
You can do this very easily and without any
sql coding with the RAC utility for S2k.
It can be used to generate dynamic crosstabs
and solve all types of problems in an easy way.
It's similar in concept to Access crosstab but
much more powerful.

RAC v2.2 and QALite @
www.rac4sql.net
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2101
by: Coleen | last post by:
Hi All :-) I found the way to get my column sum (Thanks Cor I did it a little different, but the result is what I wanted) I used: dt_stat_report_3b.Columns.Add(New DataColumn("Sum", GetType(Double), "sum(Column_10_ld_act_125_gtr_fy_fy_hh_avg)")) where dt_stat_report_3b is my datatable, Sum is my column name and sum(Column_10_ld_act_125_gtr_fy_fy_hh_avg) is the sum of the column I needed. This Does return me the correct value in the...
4
2271
by: sal | last post by:
Greets, All Converting array formula to work with datatables/dataset tia sal I finally completed a formula I was working on, see working code below. I would like to change this code so it will work with a variable mutl- row, 5 column datatable where the users select items. Anyone have any suggestions on where to start? Or changes in the current code that might be made.
1
2661
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns I entered text values and for numeric columns I entered numeric values. It works fine and pass through all the validation checks and gets inserted into the database successfully. But when I gave some junk values in the excel sheet and tried to...
13
4669
by: ppateel | last post by:
Hi, I am new to c++ and I am converting a c program to c++. I changed malloc call to new and I am getting an exception violation. Here is the relevant piece of code. Compiler vc++ 7.0 (.Net 2003) SQLINTEGER nCols; SQLINTEGER cbColDataLength; PBYTE* pColumnData = NULL;
21
2039
by: py_genetic | last post by:
Hello, I'm importing large text files of data using csv. I would like to add some more auto sensing abilities. I'm considing sampling the data file and doing some fuzzy logic scoring on the attributes (colls in a data base/ csv file, eg. height weight income etc.) to determine the most efficient 'type' to convert the attribute coll into for further processing and efficient storage... Example row from sampled file data: , ....]
1
1261
by: praneethraj | last post by:
i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore Subject Marks ScoreType A 2 Performancelevel A 123 scaledscore B 4 Performancelevel B 678 scaledscore i...
2
1944
by: truva | last post by:
Hi, I need a query which would convert Rows into Columns without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it. I have a table which is in the below format: LOGTYPE CLLI SWREL RPTDATE CNAME CVALUE avl-stplan tahlt01 EAGLE537 08/11/2007 TCPRSTSENT 12 avl-stplan tahlt01 EAGLE537 08/11/2007 IPHDRERR 88 avl-stplan tahlt01 EAGLE537 08/11/2007 IPADDRERR 65...
9
4507
by: Slain | last post by:
I need to convert a an array to a multidimensional one. Since I need to wrok with existing code, I need to modify a declaration which looks like this In the .h file int *x; in a initialize function: x = new int;
0
2381
by: Orbie | last post by:
Hi Guys, I need some help with pivoting or converting some rows on a Table into columns using SQL Server 2008! I have a Table which contains the same Products in 4 different Stores. I'm only interested in the Product_ID, Description and the Estimated Price fields for Store 1, however for the other stores (2,3 and 4) i need to display the Actaul Price and the Size on the same line of output. I've attached an example of the Table which...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11108
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10830
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9557
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7956
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.