Howto pivot in SQLite

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Howto pivot in SQLite

Sam Carleton-2
I have a invoice system where one invoice item can have one or more sum
items (images).  Example is a CD...  The invoice item is a CD, there are an
infinite numbers of images associated with that CD invoice item.  So I have
the following:

CREATE TABLE Invoice_Item (
    Invoice_Item_Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Invoice_Id INTEGER NOT NULL,
    Description VARCHAR(80) NOT NULL
)

CREATE TABLE Image (
    ImageId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Invoice_Item_Id INTEGER ,
    Image_Name VARCHAR(260) NOT NULL,
)

The Invoice_Item table has one row in it where the PKID is 1 and description
is "CD", the Image table has three rows in it, all with a Invoice_Item_Id of
1 and different image names: Img1, Img2, and Img3.

In one select statement, I want to return a view of all the Invoice_Items
for a particular Invoice such that there is one column that contains all the
image names in one string:

Invoice_Item_Id | Invoice_Id | Description | Image Names
----------------+------------+-------------+----------------------
1               |   1        | CD          | Img1, Img2, Img3

Can I do this with SQL?  If not, can I do this with a user defined
function?  The UI is going to allow the user to select the row and edit it
in another screen.

Sam
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

Simon Slavin-3

On 5 Jun 2011, at 5:47pm, Sam Carleton wrote:

> In one select statement, I want to return a view of all the Invoice_Items
> for a particular Invoice such that there is one column that contains all the
> image names in one string:
>
> Invoice_Item_Id | Invoice_Id | Description | Image Names
> ----------------+------------+-------------+----------------------
> 1               |   1        | CD          | Img1, Img2, Img3

Take a look at the group_concat() function:

http://www.sqlite.org/lang_aggfunc.html

So just like you can use max(X), or total(X) in a SELECT, you can use group_concat(Image_Name) to string a bunch of returned values together, maybe something like

SELECT group_concat(Image_Name) FROM Image WHERE Invoice_Item_Id = 1

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

Sam Carleton-2
On Sun, Jun 5, 2011 at 1:04 PM, Simon Slavin <[hidden email]> wrote:

>
> Take a look at the group_concat() function:
>
> http://www.sqlite.org/lang_aggfunc.html
>

That is PERFECT, thank you!  If the person who thought of this function
originally is reading this, thank you!!!  What a time saver!

Sam
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

Jay Kreibich
In reply to this post by Sam Carleton-2
On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the wall:

> In one select statement, I want to return a view of all the Invoice_Items
> for a particular Invoice such that there is one column that contains all the
> image names in one string:
>
> Invoice_Item_Id | Invoice_Id | Description | Image Names
> ----------------+------------+-------------+----------------------
> 1               |   1        | CD          | Img1, Img2, Img3
>
> Can I do this with SQL?

  As others have pointed out, you can, but that doesn't always make it
  a good idea.  If you're doing this just to turn around and split that
  value back up in your application code, you might want to rethink
  your data handling.  The data representation in the database is clear
  and correct.  Do you really want to alter that representation,
  smashing the image names together into a single, less clear value, just
  for the sake of making one query, rather than two?  Or even one query,
  but with an extra line or two of code in the parse function?
 
  Why not just deal with values in their native, and more correct,
  "list of images" format?

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

Sam Carleton-2
On Sun, Jun 5, 2011 at 5:44 PM, Jay A. Kreibich <[hidden email]> wrote:

> On Sun, Jun 05, 2011 at 12:47:47PM -0400, Sam Carleton scratched on the
> wall:
>
> > In one select statement, I want to return a view of all the Invoice_Items
> > for a particular Invoice such that there is one column that contains all
> the
> > image names in one string:
> >
> > Invoice_Item_Id | Invoice_Id | Description | Image Names
> > ----------------+------------+-------------+----------------------
> > 1               |   1        | CD          | Img1, Img2, Img3
> >
> > Can I do this with SQL?
>
>   As others have pointed out, you can, but that doesn't always make it
>  a good idea.  If you're doing this just to turn around and split that
>  value back up in your application code, you might want to rethink
>  your data handling.  The data representation in the database is clear
>  and correct.  Do you really want to alter that representation,
>  smashing the image names together into a single, less clear value, just
>  for the sake of making one query, rather than two?  Or even one query,
>  but with an extra line or two of code in the parse function?
>
>  Why not just deal with values in their native, and more correct,
>  "list of images" format?
>

Jay,

There is one simple reason:  Time

This is for my evening/weekend business where time is precious, I have been
dragging my feet on the current feature simply because I could not get my
head around how best to implement it.  It dawned on me today to take this
REALLY simple and less the idea approach for the general display of the
invoice and then to allow the user to select the line and bring up a
secondary dialog to manage the list of images.  It is quick and to the
point.  Once I get this in my customers heads, I will get feedback from them
for better ideas.

The bottom line is I am trying to NOT over engineer things and let my
customers drive things.  It seems to work well, they love seeing their
feedback taken to heart and I love their input:)

For now it is a good start:)

Sam
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

BareFeetWare-2
On 06/06/2011, at 8:30 AM, Sam Carleton <[hidden email]> wrote:

> allow the user to select the line and bring up a secondary dialog to manage the list of images

You could simply execute a second select when the user asks for the set of images for that invoice. It's simpler and more accurate to then iterate through the returned rows than to parse a comma separated string.

Tom
BareFeetWare
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

Sam Carleton-2
Tom,

Stop and think about what you just suggested: The invoice would look
something like this:

 |Qty | Desc  | Price   | Total
-+----+-------+----------------
+| 2  | 5x7   |  9.95   | 18.90
+| 1  | 5x7   |  9.95   | 18.90
+| 1  | 8x10  | 19.95   | 19.95
+| 2  | 16x20 | 49.00   | 98.00
+| 1  | CD    | 99.95   | 99.95

Please make note that it looks strange that there are two line items with
5x7's.  Now you add the images, you get the following:

|Qty | Desc  | Imgs             | Price   | Total
+----+-------+-----------------------------------
| 2  | 5x7   | Img1             |  9.95   | 18.90
| 1  | 5x7   | Img2             |  9.95   | 18.90
 | 1  | 8x10  | Img2             | 19.95   | 19.95
 | 2  | 16x20 | Img9             | 49.00   | 98.00
| 1  | CD    | Img1, Img2, Img3 | 99.95   | 99.95

In a perfect world, I would show the images, too.  That is coming, just not
in the initial release.  There is a lot to come, I just need to get this out
the door as quickly as possible while providing useful information to my
users.

Sam



On Sun, Jun 5, 2011 at 8:17 PM, BareFeetWare <[hidden email]>wrote:

> On 06/06/2011, at 8:30 AM, Sam Carleton <[hidden email]>
> wrote:
>
> > allow the user to select the line and bring up a secondary dialog to
> manage the list of images
>
> You could simply execute a second select when the user asks for the set of
> images for that invoice. It's simpler and more accurate to then iterate
> through the returned rows than to parse a comma separated string.
>
> Tom
> BareFeetWare
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Howto pivot in SQLite

kumardsingh
Follow below steps : Suppose My Table create table data ( ...> id int, ...> countryid int, ...> state varchar(30) ...> ); Insert Query ------------------------------------ sqlite> INSERT INTO data VALUES(56,9,'true'); sqlite> INSERT INTO data VALUES(56,54,'true'); sqlite> INSERT INTO data VALUES (57,2,'false'); sqlite> INSERT INTO data VALUES(57,9,'true') Now Pivot Query --------------------------------------- SELECT id, GROUP_CONCAT(countryid,state) AS 'state' FROM data GROUP BY id;