Go Premium for a chance to win a PS4. Enter to Win

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

sql server db select query

Hi

I how to fetch the data in sql server database like below

Ex:
I have a table t1 with two columns item and desc like below

item desc
---- ----
1    red
1    small
1    think
2    white
2    large


I need to fetch the data as below

item desc1 desc2 desc3 desc4
1    red   small think  null
2    white large null  null

I am using sql server database.

Please provide both the querys for sql server databse 2005 and 2008 versions if queries need to write differently in these versions.

Thanks.
0
GouthamAnand
Asked:
GouthamAnand
  • 6
  • 4
1 Solution
 
PortletPaulCommented:
does this need to be dynamic? i.e. will the filtering change?
0
 
Ahmed HusseinCommented:
SELECT item ,GROUP_CONCAT(type_count) FROM
(SELECT item,desc,CONCAT(desc,",",COUNT(*)) as type_count
FROM TABLE_NAME GROUP BY item,desc) as x  
GROUP BY item;
0
 
PortletPaulCommented:
see this working at: http://sqlfiddle.com/#!3/f26c7/1 
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[desc]) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item, ' + @cols + ' from 
            (
                select item, [desc]
                from table1
           ) x
            pivot 
            (
                 max([desc])
                for [desc] in (' + @cols + ')
            ) p '

--select @query
execute(@query)

Open in new window

it uses the PIVOT command
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
PortletPaulCommented:
if you have trouble using this in sql 2005 I have another approach that will work in both using case expressions and group by
0
 
PortletPaulCommented:
think group_concat is for MySQL (only)
0
 
GouthamAnandAuthor Commented:
Thank you for your responses.
But can you please suggest the case of static.   Means we assume that thre will be not more than 4 descriptions. ie 4 or less values only for any item.

Thanks.
0
 
GouthamAnandAuthor Commented:
Also, request you to provide a select query only as I need to join other tables and columns also for my original query. This requirement is part of my actual required query.
0
 
GouthamAnandAuthor Commented:
Group_concat is not working in sql server database.
0
 
PortletPaulCommented:
just re-looked at your expected results, this one produces the following:
ITEM   DESC1  DESC2    DESC3
1      red    small    think
2      large  white    (null)

see: http://sqlfiddle.com/#!3/2cd6f/8 
DECLARE @query  AS NVARCHAR(MAX)

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN [desc] ELSE null END) AS Desc'
            + convert(varchar,number)
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(c)
                                    FROM (
                                          SELECT
                                                item
                                              , count(*) AS c
                                          FROM table1
                                          WHERE 1=1  /* i.e. filter the data here */
                                          GROUP BY
                                                item
                                        ) AS x
                                    )
           )


select @query = 

  'WITH CTE as ('
+ '        select'
+ '            table1.item, table1.[desc]'
+ '          , row_number() over (partition by table1.Item order by table1.[desc]) as row_ref'
+ '        from table1'
+ '        where 1=1 /* i.e. filter the data here */'
+ '       )'
+ 'select Item, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from CTE group by Item'

--select @query -- uncomment to inspect what the variable contains
exec(@query)
;

Open in new window

0
 
PortletPaulCommented:
>>But can you please suggest the case of static.  
apologies, I missed this request. The following is static and assumes at most 4 columns
WITH CTE
AS (
	SELECT
		  table1.item
		-- , table2.whatever -- add fields as needed
		, table1.[desc]
		, row_number() OVER (
			PARTITION BY table1.Item ORDER BY table1.[desc]
			) AS row_ref
	FROM table1
	-- INNER JOIN table2 on table1.id = table2.fk
	-- WHERE 1 = 1 /* i.e. if you need to filter the data, do it here */
	)
SELECT
	  Item
        --, whatever
	, max(CASE WHEN row_ref = 1 THEN [desc] ELSE NULL END) AS Desc1
	, max(CASE WHEN row_ref = 2 THEN [desc] ELSE NULL END) AS Desc2
	, max(CASE WHEN row_ref = 3 THEN [desc] ELSE NULL END) AS Desc3
	, max(CASE WHEN row_ref = 4 THEN [desc] ELSE NULL END) AS Desc4
FROM CTE
GROUP BY
   Item
--, whatever

Open in new window

http://sqlfiddle.com/#!3/2cd6f/10

If you are adding further tables and fields, you can gather the base data within the CTE just like a normal select. Once you have that done then you also need to amend the selection list and group by list of the lower query.

If you are having issues with this please let us know.

Also, just another thing, in your sample data you use the field name [desc] "DESC" is used in SQL to direct an order by so if the field name [desc] is really what you use please ensure it is surround by the square brackets.

and yes, group_concat() is MySQL only - it does not work in sql server. (I did mention this.)
0
 
GouthamAnandAuthor Commented:
Thank you very much. I change the script accordingly to my requirement and  it works perfectly for my requirement.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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