Header in fields with fields value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Header in fields with fields value

Author

Topic

makis_best
Starting Member

8 Posts

Posted - 2015-01-29 : 05:26:13

Hi

I have a table returning results like this


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

How I can make the result return like this way?


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

Here is the code I use

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)

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 07:06:29

I can't quite see what kind of change you want to make to your output data. For one thing your headers in columns don't line up in your posting so it's difficult to see what data goes with what column. Why not explain in words what you're trying to accomplish and post the query that you have built so far.

makis_best
Starting Member

8 Posts

Posted - 2015-01-29 : 08:10:01

I want the category field to be alone in the top with no subcategory
of any group

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 11:40:45

Really difficult to see what you mean. Three things you need to do:

1. Your posted data still doesn't line up with the column headers. Please fix that in your post.
2. Post the CREATE TABLE statement(s) for the tables involved
3. Post the query you are using to generate the data you posted.

jleitao
Posting Yak Master

100 Posts

Posted - 2015-01-29 : 12:39:22

i'm not sure what you realy have in the table, however try this:

Select
*
from TABLE
Order by Raw1, SubCategory

if doesn't work, respond to the previous gbritton post

------------------------
PS - Sorry my bad english

makis_best
Starting Member

8 Posts

Posted - 2015-01-30 : 03:41:46

First.
I have to thank you all for your time spend for me.

Second.
I did the changes you ask me in the first post.
Sorry I wasn't so clear about that.

As you can see when a category change I want to add a new record
with auto increment and no subcategory.

jleitao
Posting Yak Master

100 Posts

Posted - 2015-01-30 : 07:44:56

WITH DATA AS(

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)

), CATEGORIES as (

SELECT
Row1,
ParentID,
Category,
MIN(publish) as publish -- Not sure if is that you need
from DATA
GROUP BY Row1,
ParentID,
Category
)

SELECT
Row1,
Row1 as Row2,
ParentID + '' + ParentID as ERPID,
ParentID,
ParentID as LevelID,
Category,
NULL as SubCategory, -- or '' as SubCategory,
GETDATE() as DDate,
publish
FROM CATEGORIES

UNION ALL

SELECT
Row1,
Row2,
ERPID,
ParentID,
LevelID,
Category,
SubCategory,
DDate,
publish
FROM DATA

if doesn't work send us the script to create the tables and the insert command with a few lines to example.

------------------------
PS - Sorry my bad english

makis_best
Starting Member

8 Posts

Posted - 2015-02-02 : 02:56:24

I make the change I want Bolt so everyone can understand.
If you count the lines I return you will see there is
a difference.

I group the data with category field.

I want above any group a new line with the name of the group.

Sorry for my bad English

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-02 : 03:38:39

[code]
declare @table table(
Row1 int
,Row2 int
,ERPID int
,ParentID int
,LevelID int
,Category varchar(20)
,SubCategory varchar(20)
,DDate datetime
,publish int)

insert into @table select
1,1,10152159,1015,2159,'LOCTITE','LOCTITE1','2015-01-29 12:10',0 union all select
1,2,10152134,1015,2134,'LOCTITE','LOCTITE2','2015-01-29 12:10',0 union all select
1,3,10152157,1015,2157,'LOCTITE','LOCTITE3','2015-01-29 12:10',0 union all select
2,1,10062003,1006,2003,'COMPUTER','COMPUTER1','2015-01-29 12:10',1 union all select
2,2,10062148,1006,2148,'COMPUTER','COMPUTER2','2015-01-29 12:10',1 union all select
2,3,10062026,1006,2026,'COMPUTER','COMPUTER3','2015-01-29 12:10',1 union all select
3,1,10142003,1014,2003,'PARTS','PARTS1','2015-01-29 12:10',0 union all select
3,2,10142164,1014,2164,'PARTS','PARTS2','2015-01-29 12:10',0 union all select
4,1,10202175,1020,2175,'PRINTERS','PRINTERS1','2015-01-29 12:10',1 union all select
4,2,10202177,1020,2177,'PRINTERS','PRINTERS2','2015-01-29 12:10',1 union all select
4,3,10202181,1020,2181,'PRINTERS','PRINTERS3','2015-01-29 12:10',1 union all select
4,4,10202186,1020,2186,'PRINTERS','PRINTERS4','2015-01-29 12:10',1 union all select
5,1,10012004,1001,2004,'SCANERS','SCANERS1','2015-01-29 12:10',0 union all select
5,2,10012012,1001,2012,'SCANERS','SCANERS2','2015-01-29 12:10',1 union all select
5,3,10012015,1001,2015,'SCANERS','SCANERS3','2015-01-29 12:10',1 union all select
5,4,10012016,1001,2016,'SCANERS','SCANERS4','2015-01-29 12:10',0 union all select
5,5,10012029,1001,2029,'SCANERS','SCANERS5','2015-01-29 12:10',1 union all select
5,6,10012032,1001,2032,'SCANERS','SCANERS6','2015-01-29 12:10',0

select Row1, Row1, ERPID, ParentID, ParentID, Category, '', DDate, publish
from @table a
where row2 = 1
union all
select *
from @table
order by 1,2, 7
[/code]

makis_best
Starting Member

8 Posts

Posted - 2015-02-03 : 03:03:24

Finaly....

Thank you waterduck
You are the best... exactly what I wanted.

Sorry for my bad English

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources