Field as Group header

Question

Post reply

Field as Group header

makis_best

Mr or Mrs. 500

Points: 528
More actions
January 29, 2015 at 3:55 am

#302669

Hi.
I am new in SQL and I want to ask how i can do that.
I have a table returning results like that
Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1
2 ||2 ||10062148||1006 ||2148 ||COMPUTER||COMPUTER2||29/01/2015 12:10||1
2 ||3 ||10062026||1006 ||2026 ||COMPUTER||COMPUTER3||29/01/2015 12:10||1
3 ||1 ||10142003||1014 ||2003 ||PARTS ||PARTS1 ||29/01/2015 12:10||0
3 ||2 ||10142164||1014 ||2164 ||PARTS ||PARTS2 ||29/01/2015 12:10||0
4 ||1 ||10202175||1020 ||2175 ||PRINTERS||PRINTERS1||29/01/2015 12:10||1
4 ||2 ||10202177||1020 ||2177 ||PRINTERS||PRINTERS2||29/01/2015 12:10||1
4 ||3 ||10202181||1020 ||2181 ||PRINTERS||PRINTERS3||29/01/2015 12:10||1
4 ||4 ||10202186||1020 ||2186 ||PRINTERS||PRINTERS4||29/01/2015 12:10||1
5 ||1 ||10012004||1001 ||2004 ||SCANERS ||SCANERS1||29/01/2015 12:10||0
5 ||2 ||10012012||1001 ||2012 ||SCANERS ||SCANERS2||29/01/2015 12:10||1
5 ||3 ||10012015||1001 ||2015 ||SCANERS ||SCANERS3||29/01/2015 12:10||1
5 ||4 ||10012016||1001 ||2016 ||SCANERS ||SCANERS4||29/01/2015 12:10||0
5 ||5 ||10012029||1001 ||2029 ||SCANERS ||SCANERS5||29/01/2015 12:10||1
5 ||6 ||10012032||1001 ||2032 ||SCANERS ||SCANERS6||29/01/2015 12:10||0
But I want to look like that
Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1
2||1||10062003||1006||2003||COMPUTER||COMPUTER1||29/01/2015 12:10||1
2||2||10062148||1006||2148||COMPUTER||COMPUTER2||29/01/2015 12:10||1
2||3||10062026||1006||2026||COMPUTER||COMPUTER3||29/01/2015 12:10||1
3||3||10141014||1014||1014||PARTS||||29/01/2015 12:10||0
3||1||10142003||1014||2003||PARTS||PARTS1||29/01/2015 12:10||0
3||2||10142164||1014||2164||PARTS||PARTS2||29/01/2015 12:10||0
4||4||10201020||1020||1020||PRINTERS||||29/01/2015 12:10||1
4||1||10202175||1020||2175||PRINTERS||PRINTERS1||29/01/2015 12:10||1
4||2||10202177||1020||2177||PRINTERS||PRINTERS2||29/01/2015 12:10||1
4||3||10202181||1020||2181||PRINTERS||PRINTERS3||29/01/2015 12:10||1
4||4||10202186||1020||2186||PRINTERS||PRINTERS4||29/01/2015 12:10||1
5||5||10011001||1001||1001||SCANERS||||29/01/2015 12:10||0
5||1||10012004||1001||2004||SCANERS||SCANERS1||29/01/2015 12:10||0
5||2||10012012||1001||2012||SCANERS||SCANERS2||29/01/2015 12:10||1
5||3||10012015||1001||2015||SCANERS||SCANERS3||29/01/2015 12:10||1
5||4||10012016||1001||2016||SCANERS||SCANERS4||29/01/2015 12:10||0
5||5||10012029||1001||2029||SCANERS||SCANERS5||29/01/2015 12:10||1
5||6||10012032||1001||2032||SCANERS||SCANERS6||29/01/2015 12:10||0
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

you should check out how to post questions, including table definitions, sample data in a ready to use format.

I've done similar queries like so (not sure if it would work for you tho, not tested, etc...):

select row1, row2, erpid, parentid, levelid, category, subcategory, ddate, publish

from

(

select '1-detail' lvl, row1, row2, erpid, parentid, levelid, category, subcategory, ddate, publish

from your_table

union

select distinct '0-header' lvl, row1, row1 as row2, row, parentid + parentid as erpid, parentid,

parentid as levelid, category, '' as subcategory, ddate, publish

from your_table

) unioned_stuff

order by lvl, row1, row2

edit to remove an 'etc'

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

This is a fairly simple adjacency list. The key here will be knowing what the raw data looks like and what the columns Row1 and Row2 are supposed to contain. For example, is Row1 supposed to be some sort of "Hierarchical Level" and Row2 is supposed to represent a node# within that level or what?

My recommendation would be to post a decent portion of the raw data in the format outlined by the article located at the first link under "Helpful Links" in my signature line below along with the code that created your result set so that we can better help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

makis_best Mr or Mrs. 500 Points: 528 More actions · Answer 3

The first column is just an auto incement number "Row1" and "Row2" is auto increment of the subcategories of the current category

like tree view:

1-

|- 1.1

|- 1.2

2-

|- 2.1

|- 2.2

the table is short by category name

Here is my code

SELECT
DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr
ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
GetDate() AS DDate, CASE WHEN T1.ID IN
(SELECT ID
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) + RIGHT(T1.ID, 4) IN
(SELECT DISTINCT ParentID + ParentID
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) IN
(SELECT DISTINCT LEFT(ID, 4)
FROM Ctgry1 LEFT JOIN
Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
PrLLines ON (PrLLines.MaterialAA = Material.AA)
WHERE PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 0 ELSE 0 END AS publish
FROM clroot.Ctgry1 LEFT JOIN
clroot.Ctgry1 AS T1 ON T1.ParentID = clroot.Ctgry1.ID
WHERE (T1.Descr IS NOT NULL)