SQL or StoreProc for a strange requirement

[gangaprasad]

Hi,

Here I have a strange requirement - I have a table with 2 columns, it is basically 1 to many relationship table. e.g.

col1 col2
=== ===
id1 map1
id1 map2
id2 map2
id2 map3
id3 map1
id3 map2
id3 map3

Now I need to display in a spreadsheet as below

-----------------------------------------------------
Name Relation_1 Relation_2 Relation_3
-----------------------------------------------------
id1 Y Y N
id2 N Y Y
id3 Y Y Y

Now I need a SQL statement or Stored Proc to return be 3 rows with above mentioned data.

I hope I made clear my point.

Thanks in advance.

[antares686]

First a Stored Procedure is a SQL statement or batch of statements saved together. It is preferred to use them if you can as it can store an execution plan with the SP which adds a bit of power to the processes execution. In addition, you can grant just execution rights to the SP instead of rights on the underlying table to control access and secure the data against unneeded information reads.

What you are looking for is called a Pivot table. In this case it is easy to do since you have a limited number of items to contend with.

CASE statements allow for conditional handling of data which is why you use them for pivots. For you needs this would be the most probable answer.

SELECT
col1 AS [Name],
(CASE SUM(CASE col2 WHEN 'map1' THEN 1 ELSE 0 END) WHEN 0 THEN 'N' ELSE 'Y' END) AS Relation_1,
(CASE SUM(CASE col2 WHEN 'map2' THEN 1 ELSE 0 END) WHEN 0 THEN 'N' ELSE 'Y' END) AS Relation_2,
(CASE SUM(CASE col2 WHEN 'map3' THEN 1 ELSE 0 END) WHEN 0 THEN 'N' ELSE 'Y' END) AS Relation_3
FROM
tblNameHere
GROUP BY
col1

Then you can wrap the above in

CREATE PROCEDURE usp_RelationPivot
AS

[above query]
GO

Hope that all helps.

[gangaprasad]

That was an impressive response. Thanks.
I did figure some other solution using intermediate tables and inserts/updates, which is not good, as below

CREATE TABLE #tmpTbl
(
colo1 int, Relation_1 char, Relation_2 char, Relation_3 char
)
INSERT INTO #tmpTbl (col1) SELECT distinct(col1) From mainTable
UPDATE #tmpTbl SET Relation_1 = 'X' WHERE col1 in (select distinct(col1) From mainTable WHERE col1=1)
UPDATE #tmpTbl SET Relation_2 = 'X' WHERE col2 in (select distinct(col1) From mainTable WHERE col2=1)
UPDATE #tmpTbl SET Relation_3 = 'X' WHERE col3 in (select distinct(col1) From mainTable WHERE col3=1)
SELECT * FROM #tmpTbl
DROP TABLE #tmpTbl

Substituting X for Y and nothing (NULL) for N

I know it is not the most efficient way but, that's all I coulkd come up, with my limited SQL knowledge.

Once again thanks for the solution.

[antares686]

Whether your answer is right or wrong, or whether it is the most optimized or not it doesn't matter, as long as you make an attempt. This shows you want to utilize what you know and by asking shows you want to learn more.

Keep up the good work.

:) :) :)