I have a sql that returns a number of columns with values. I would like to insert a blank line after each new customer. How do I do that with a simple sql? How it looks today:
But I want it to be like this
asked 23 Oct '14, 04:29 Rolle |
While in general I share's Thomas's statement that this might be more a task for a report generator or the like, here's a different approach using OLAP functions and the builtin rowgenerator table. Without knowing your table schema and actual query, I have tried something similar based on SA's demo database and its Products table, as that table has several rows with the same Name and has therefore a somewhat similar result set:
returns the following: Name,Description,Size Baseball Cap Cotton Cap One size fits all Baseball Cap Wool cap One size fits all Shorts Cotton Shorts Medium Sweatshirt Hooded Sweatshirt Large Sweatshirt Zipped Sweatshirt Large Tee Shirt Crew Neck One size fits all Tee Shirt Tank Top Small Tee Shirt V-neck Medium Visor Cloth Visor One size fits all Visor Plastic Visor One size fits all First step: Number the resulting groups (i.e. same values for "Name") and according rows with the help of OLAP functions DENSE_RANK() and ROW_NUMBER();
returns 1 1 Baseball Cap Cotton Cap One size fits all 1 2 Baseball Cap Wool cap One size fits all 2 3 Shorts Cotton Shorts Medium 3 4 Sweatshirt Hooded Sweatshirt Large 3 5 Sweatshirt Zipped Sweatshirt Large 4 6 Tee Shirt Crew Neck One size fits all 4 7 Tee Shirt Tank Top Small 4 8 Tee Shirt V-neck Medium 5 9 Visor Cloth Visor One size fits all 5 10 Visor Plastic Visor One size fits all Second step: For each group, add an according "empty row" from rowgenerator using an UNION ALL query:
returns GroupNo RowNo Name Description Size 1 (NULL) (NULL) (NULL) (NULL) 1 1 Baseball Cap Cotton Cap One size fits all 1 2 Baseball Cap Wool cap One size fits all 2 (NULL) (NULL) (NULL) (NULL) 2 3 Shorts Cotton Shorts Medium 3 (NULL) (NULL) (NULL) (NULL) 3 4 Sweatshirt Hooded Sweatshirt Large 3 5 Sweatshirt Zipped Sweatshirt Large 4 (NULL) (NULL) (NULL) (NULL) 4 6 Tee Shirt Crew Neck One size fits all 4 7 Tee Shirt Tank Top Small 4 8 Tee Shirt V-neck Medium 5 (NULL) (NULL) (NULL) (NULL) 5 9 Visor Cloth Visor One size fits all 5 10 Visor Plastic Visor One size fits all Third step: Finally build the query without the row numbers by using the previous query as a derived query. (These are basically two steps: Build one derived query to number the resulting rows, and then select from that derived query to omit the row numbers - but ordered by those row numbers):
This finally returns Name Description Size (NULL) (NULL) (NULL) Baseball Cap Cotton Cap One size fits all Baseball Cap Wool cap One size fits all (NULL) (NULL) (NULL) Shorts Cotton Shorts Medium (NULL) (NULL) (NULL) Sweatshirt Hooded Sweatshirt Large Sweatshirt Zipped Sweatshirt Large (NULL) (NULL) (NULL) Tee Shirt Crew Neck One size fits all Tee Shirt Tank Top Small Tee Shirt V-neck Medium (NULL) (NULL) (NULL) Visor Cloth Visor One size fits all Visor Plastic Visor One size fits all answered 23 Oct '14, 08:14 Volker Barth |
Assuming that the sample data is a complete and perfect representation of the problem, here is a query that could probably be simplified (because it just feels too complex :)... CREATE TABLE t ( inserted_order INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, customer VARCHAR ( 10 ) NOT NULL, value1 VARCHAR ( 10 ) NOT NULL, value2 VARCHAR ( 10 ) NOT NULL, value3 VARCHAR ( 10 ) NOT NULL ); INSERT t VALUES ( DEFAULT, 'volvo', 'red', '23', '32' ); INSERT t VALUES ( DEFAULT, 'volvo', 'blue', '29', '39' ); INSERT t VALUES ( DEFAULT, 'volvo', 'pink', '43', '51' ); INSERT t VALUES ( DEFAULT, 'toyota', 'black', '26', '61' ); INSERT t VALUES ( DEFAULT, 'toyota', 'green', '19', '82' ); INSERT t VALUES ( DEFAULT, 'toyota', 'black', '11', '71' ); INSERT t VALUES ( DEFAULT, 'saab', 'black', '26', '61' ); INSERT t VALUES ( DEFAULT, 'saab', 'green', '19', '82' ); INSERT t VALUES ( DEFAULT, 'saab', 'black', '11', '71' ); COMMIT; SELECT customer, value1, value2, value3 FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY subset_customer DESC, subset_order, inserted_order ) AS row_number, IF subset_order = 1 THEN subset_customer ELSE '---' ENDIF AS customer, value1, value2, value3 FROM ( SELECT customer AS subset_customer, 1 AS subset_order, inserted_order, value1, value2, value3 FROM t UNION ALL SELECT customer AS subset_customer, 2, 0, '---', '---', '---' FROM t GROUP BY customer ) AS row_set ORDER BY subset_customer DESC, subset_order, inserted_order ) AS report ORDER BY row_number; customer value1 value2 value3 ---------- ---------- ---------- ---------- volvo red 23 32 volvo blue 29 39 volvo pink 43 51 --- --- --- --- toyota black 26 61 toyota green 19 82 toyota black 11 71 --- --- --- --- saab black 26 61 saab green 19 82 saab black 11 71 --- --- --- --- answered 23 Oct '14, 08:10 Breck Carter |
I agree with the sentiment that this doesn't feel like a SQL problem, but hey, I wrote a calendar generator in SQL once (and a COBOL scanner in COBOL--but that's another story). Here's my entry. It inserts an extra row for each distinct value (using Breck's approach) then sorts. I might be missing why the ROW_NUMBER is needed.
answered 26 Oct '14, 22:20 Ivan T. Bowman 1
That does insert an empty row after each group of values, so I guess to sort it the other way, you simply have to switch 0 and 1:
Otherwise, that's obviously a waaaay better solution than Breck's and mine - and much easier to understand as well... - thanks for sharing:)
(27 Oct '14, 04:54)
Volker Barth
Replies hidden
1
but hey, I wrote a calendar generator in SQL once (and a COBOL scanner in COBOL--but that's another story) Ahh its the old problem isn't it - once you've got a hammer, every problem starts to look like a nail!
(27 Oct '14, 05:23)
Justin Willey
Well done, and it certainly satisfies the wish for "a query that could probably be simplified (because it just feels too complex :)..." select case ord when 0 then Name end as name, Description, Size from ( select 0 as ord, Name, Description, Size from Products union all select 1 as ord, name, null, null from Products group by Name ) D order by D.name, D.ord, D.description; name,Description,Size 'Baseball Cap','Cotton Cap','One size fits all' 'Baseball Cap','Wool cap','One size fits all' ,, 'Shorts','Cotton Shorts','Medium' ,, 'Sweatshirt','Hooded Sweatshirt','Large' 'Sweatshirt','Zipped Sweatshirt','Large' ,, 'Tee Shirt','Crew Neck','One size fits all' 'Tee Shirt','Tank Top','Small' 'Tee Shirt','V-neck','Medium' ,, 'Visor','Cloth Visor','One size fits all' 'Visor','Plastic Visor','One size fits all' ,,
(27 Oct '14, 08:46)
Breck Carter
Replies hidden
2
Well, isn't that a fine sample of the general division of labour: No matter how overly-complex the queries are we figure out, Ivan (aka the query processing engine) will do the optimization:)
(27 Oct '14, 10:26)
Volker Barth
Good point @Justin. Either that or when I pick up a heavy tool, I just want to start hitting anything nearby to see what happens.
(29 Oct '14, 10:45)
Ivan T. Bowman
Glad you wrote anything:)
(29 Oct '14, 11:37)
Volker Barth
|
I thinks this is more of a front end problem that doesn't need to be fixed in a query. Anyway you could try to figure something out with the help of grouping sets. You could then group by customer and all other columns are set to NULL. HTH answered 23 Oct '14, 06:50 Thomas Dueme... |