[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Writing Two Sql Server records as one

I have a query...

select fkItemID,fkItemHierarchyID from tblItemCategory where fkItemID = 831

The dataset returned is

fkItemID      fkItemHierarchyID
831                    220
831                    366

I want the dataset to look like this

fkItemId      Category1      Category2
831                  220                   366


Does someone know a query I could use to do that.

Thanks.
0
sherbug1015
Asked:
sherbug1015
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
different options,  use dynamic PIVOT if you dont know the no of categories

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
0
 
ralmadaCommented:
How many categories do you have? assuming 4, try the below (add more [numbers] columns  if you have more categories).

select fkItemID, [1], [2], [3], [4]
from (
	selec fkItemID, fkItemHierarchyID, row_number() over (partition by fkItemID order by fkItemID) rn
	from tblItemCategory 
) o
pivot(max(fkItemHierarchyID) for rn in ([1], [2], [3], [4])) p 

Open in new window

0
 
ralmadaCommented:
And if you don't know how many categories columns you could have, then you will have to go dynamic

Declare @strSQL varchar(max)        
Declare @cols varchar(2000)        
  
      
SELECT  @cols = STUFF(( SELECT 
                                '],[' + cast(rn as varchar(max))        
                        From (
				selec distinct row_number() over (partition by fkItemID order by fkItemID) rn
				from tblItemCategory 
			) a
                        ORDER BY 1        
                        FOR XML PATH('')        
                      ), 1, 2, '') + ']'        
         
         
set @strSQL = 'select fkItemID, ' + @cols +       
' from (       
	selec fkItemID, fkItemHierarchyID, row_number() over (partition by fkItemID order by fkItemID) rn
	from tblItemCategory 
) o       
pivot(max(fkItemHierarchyID) for rn in (' + @cols + ')) p'
     
exec(@strSQL) 

Open in new window

0
 
hpdvs2Commented:
I don't know an easy way to do this, but that is for good reason.  I'm wondering why you would want to add create dynamic fields for category 1/2.  I'm presuming that in your system, an Item can exist in more than 2 categories anyway.  

In order to read this in your code, your going to have to create a loop to go through in one of two ways.  

List<int> Categories = new ...; // in all the code samples below
rs = Recordset...; // also in all code below.

for(int i = 1; i < rs.fieldcount; i++)
{
   Categories.Add((int)rs[i]);
}

Open in new window


--OR--
for(int i = 1; i =< rs.fieldcount; i++)
{
   Categories.Add((int)rs["Category"+i]);
}

Open in new window


When all you have to do now, is this:
while(rs.next())
{
   Categories.add((int)rs[1]);
}

Open in new window



Considering that this method requires less code, and puts less stress on the db, I'm wondering what value your code would get out of returning by field list instead of row list.
0
 
sherbug1015Author Commented:
Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now